Databases
StackOverflow2013
Postsdbo
POIs it possible to do this in SQL (summing rows over different criteria per user)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs it possible to do this in SQL (summing rows over different criteria per user)
    primarykey
    data
    text
    <p>I have an SQL table that looks like this:</p> <pre><code>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); </code></pre> <p>I further have this table:</p> <pre><code>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'); </code></pre> <p>I want to write a query that returns DISTINCT ROWS with the following columns:</p> <ul> <li>the <strong>user_id</strong></li> <li>the <strong>sum of the weights</strong> in table <code>diet_watch</code> between the specified dates in table summing_period (for the user_id)</li> </ul> <p>So the result of the query based on the data in table summing period should be:</p> <pre><code>1,350.0 2,128.0 3,224.0 </code></pre> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser272735
    1. USHomunculus Reticulli
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs it possible to do this in SQL (summing rows over different criteria per user)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIs it possible to do this in SQL (summing rows over different criteria per user)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POIs it possible to do this in SQL (summing rows over different criteria per user)
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload