Is it possible to do this in SQL (summing rows over different criteria per user)

Question

I have an SQL table that looks like this:

CREATE TABLE diet_watch ( entry_date date NOT NULL, user_id int default 1, weight double precision NOT NULL ); INSERT INTO diet_watch VALUES ('2001-01-01', 1, 128.2); INSERT INTO diet_watch VALUES ('2001-01-02', 1, 121.2); INSERT INTO diet_watch VALUES ('2001-01-03', 1, 100.6); INSERT INTO diet_watch VALUES ('2001-01-04', 1, 303.7); INSERT INTO diet_watch VALUES ('2001-01-05', 1, 121.0); INSERT INTO diet_watch VALUES ('2001-01-01', 2, 121.0); INSERT INTO diet_watch VALUES ('2001-01-06', 2, 128.0); INSERT INTO diet_watch VALUES ('2001-01-07', 2, 138.0); INSERT INTO diet_watch VALUES ('2001-01-01', 3, 128.2); INSERT INTO diet_watch VALUES ('2001-01-02', 3, 125.5); INSERT INTO diet_watch VALUES ('2001-01-03', 3, 112.8); INSERT INTO diet_watch VALUES ('2001-01-06', 3, 111.2);

I further have this table:

CREATE TABLE summing_period ( user_id INT NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL); insert into summing_period VALUES (1, '2001-01-01', '2001-01-03'); insert into summing_period VALUES (2, '2001-01-02', '2001-01-06'); insert into summing_period VALUES (3, '2001-01-03', '2001-01-06');

I want to write a query that returns DISTINCT ROWS with the following columns:

the user_id
the sum of the weights in table diet_watch between the specified dates in table summing_period (for the user_id)

So the result of the query based on the data in table summing period should be:

1,350.0 2,128.0 3,224.0

Unfortunately, this time, I have reached the limit of my SQLfu - and I no idea how to even get started in writing the SQL. Ideally, the solution should be ANSI SQL (i.e. db agnostic). however, since I am developing to a PostgreSQL 8.4 backend, if the solution is db centric, it must at least run on PG.

csd · Accepted Answer · 2012-07-17 21:42:27Z

4

SELECT sp.user_id, SUM(dw.weight) FROM summing_period sp, diet_watch dw WHERE dw.user_id = sp.user_id AND dw.entry_date >= sp.start_date AND dw.entry_date <= sp.end_date GROUP BY sp.user_id

What this query does is join each diet_watch row to the row in summing_period that matches its user_id and whose date falls in the summing_period's range.

The SELECT then asks for the SUM of the weights for each different user_id (as a result of the GROUP BY user_id).

edited Jul 17, 2012 at 21:42

answered Jul 17, 2012 at 21:34

csd

1,79410 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

RThomas Over a year ago

I wasn't sure if a join as used here would work with Postgres (I'm not as familiar with it as other flavors of SQL) but I checked on sqlfiddle and this works as well... and is probably a better way than my own answer. +1

RThomas Over a year ago

this works, and you can test other flavors of sql here: sqlfiddle.com/#!1/26d0c/5

csd Over a year ago

Whoa... SQL Fiddle is awesome! How have I not come across that before!? (Thanks, RThomas!)

RThomas Over a year ago

Awesome - yes - but don't feel bad, it's pretty new. It was built by @Jake Feasel a few months ago to demonstrate sql based answers here on S.O. Making Jake Feasel a royal knight of the S.O. round-table in my opinion.

Homunculus Reticulli Over a year ago

Wow, it looks so simple. I almost feel foolish for having asked the question. Thanks though - SQL has always been my achilles heel.

Collectives™ on Stack Overflow

Is it possible to do this in SQL (summing rows over different criteria per user)

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related