Databases
StackOverflow2013
Postsdbo
POSQL Server select with multiple groupings
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server select with multiple groupings
    primarykey
    data
    text
    <p>I have two tables describing users and their payments:</p> <pre><code>CREATE TABLE test_users (id int IDENTITY NOT NULL, name varchar(25), PRIMARY KEY (id)); CREATE TABLE test_payments (id int IDENTITY NOT NULL, user_id int NOT NULL, money money NOT NULL, date datetime NOT NULL, PRIMARY KEY (id)); INSERT INTO test_users (name) VALUES ('john'); INSERT INTO test_users (name) VALUES ('peter'); INSERT INTO test_payments (user_id, money, date) VALUES (1, $1, CONVERT(datetime, '15.12.2012')); INSERT INTO test_payments (user_id, money, date) VALUES (1, $2, CONVERT(datetime, '16.12.2012')); INSERT INTO test_payments (user_id, money, date) VALUES (2, $1, CONVERT(datetime, '16.12.2012')); INSERT INTO test_payments (user_id, money, date) VALUES (2, $3, CONVERT(datetime, '17.12.2012')); INSERT INTO test_payments (user_id, money, date) VALUES (1, $1, CONVERT(datetime, '19.12.2012')); </code></pre> <p>Table test_users:</p> <pre><code>id name ------------- 1 john 2 peter </code></pre> <p>Table test_payments:</p> <pre><code>id user_id money last_activity --------------------------------------- 1 1 1.0000 2012-12-15 2 1 2.0000 2012-12-16 3 2 1.0000 2012-12-16 4 2 3.0000 2012-12-17 5 1 1.0000 2012-12-19 </code></pre> <p>I need to make a users statistic which will show me : </p> <ol> <li>username </li> <li>total fee for a period of time </li> <li>the date of the last user's activity (general, not for a time period).</li> </ol> <p>For example taking the period 15-18.12.12 I expect the following results:</p> <pre><code>name total last_activity -------------------------------- peter $4 2012-12-17 john $3 2012-12-19 </code></pre> <p>I've tried the following query:</p> <pre><code>SELECT u.*, SUM(p.money) total, MAX(p.date) last_activity FROM test_users u JOIN test_payments p ON u.id= p.user_id WHERE p.date BETWEEN CONVERT(datetime, '15.12.2012') AND CONVERT(datetime, '18.12.2012') GROUP BY u.id, u.name ORDER BY total DESC; </code></pre> <p>but getting wrong result for last_activity as it is also in the date range:</p> <pre><code>id name total last_activity -------------------------------- 2 peter 4.0000 2012-12-17 1 john 3.0000 2012-12-16 </code></pre> <p>Please suggest a solution.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USgator88
    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
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server select with multiple groupings
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Server select with multiple groupings
      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