CODEWITHSUNDEEP
mysqlsql
Jacksonkr
Jacksonkr

Reputation: 32227

mysql SUM aggregate with ORDER BY

I'm using SUM to add up one of the fields but for the rest of the fields I would like those coming from the latest entry.

Current Query:

SELECT cu.*,
SUM(cu.use_count) AS total_use_count
FROM coupons_used cu
GROUP BY cu.coupon_id;

Current result:

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [timestamp] => 2015-05-08 04:52:31
            [coupon_id] => 133
            [user_id] => 104
            [use_count] => 1
            [redemption_code] => 123412341234
            [total_use_count] => 2
        )

)

That timestamp is from the first entry and I'd like it to be from the last. I've tried adding ORDER BY cu.timestamp both ASC and DESC but I get the same result. total_use_count needs to stay as is but how do I get the rest of the fields to be from the last entry (per grouping of course)?

Upvotes: 0

Views: 60

Answers (3)

O. Jones
O. Jones

Reputation: 108696

To get the sum you do this:

 SELECT SUM(use_count) AS total_use_count, coupon_id
   FROM coupons_used
  GROUP BY coupon_id

But, to get the latest entry for each coupon_id you do this.

  SELECT c.*
    FROM coupon_id AS c
    JOIN (  SELECT MAX(id) AS id
              FROM coupon_id 
             GROUP BY coupon_id
         ) m ON c.id = m.id

The inner SELECT gets a table with the id numbers for the rows that are the latest entries for each coupon_id. Then joining that to the main table pulls out the detail to from the main table.

Finally, you gotta join all this together to get one row for each coupon_id, with the sum and the latest. 

SELECT c.*, a.total_use_count
  FROM coupon_id c
  JOIN (  SELECT MAX(id) AS id
              FROM coupon_id 
             GROUP BY coupon_id
       ) m ON c.id = m.id
  JOIN (
          SELECT SUM(use_count) AS total_use_count, coupon_id
            FROM coupons_used
        GROUP BY coupon_id
        ) a ON a.coupon_id = c.coupon_id

The way you were attempting to use GROUP BY made use of a troublesome MySQL extension to the standard GROUP BY. That's why the wrong row came back. You can read this for more information. http://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html

Upvotes: 1

spencer7593
spencer7593

Reputation: 108410

If the (coupon_id,timestamp) tuple is unique (that is, if we are guaranteed that there are not two (or more) rows with the same timestamp value for a given coupon_id, at least for the "latest" timestamp)....

SELECT c.id
     , c.timestamp
     , c.coupon_id
     , c.user_id
     , c.use_count
     , c.redemption_code
     , m.total_use_count
  FROM ( SELECT MAX(mt.timestamp) AS latest_timestamp
              , SUM(mt.use_count) AS total_use_count
              , mt.coupon_id
           FROM coupons_used mt
          GROUP BY mt.coupon_id
       ) m
  JOIN coupons_uses c
    ON c.coupon_id = m.coupon_id
   AND c.timestamp = m.latest_timestamp

If the (coupon_id,timestamp) isn't unique; if there could be two rows with the same timestamp for given coupon_id, and if we want to return only a single row for each coupon_id...

We can make use of a MySQL extension to the GROUP BY and add to the end of the query:

 GROUP BY c.coupon_id

Upvotes: 4

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726639

When you use GROUP BY the columns that you return should be one of two categories:

  • A column computed with an aggregate function, or
  • A column that is part of GROUP BY list.

MySql relaxes this restriction by letting you put any columns in a select list, but this comes with understanding that any row in a group may be chosen by the engine at random to supply the value for your column.

If you want the last time stamp, put a MAX function on it:

SELECT 
    coupon_id
,   SUM(cu.use_count) AS total_use_count
,   MAX(cu.timestamp) AS timestamp
FROM coupons_used cu
GROUP BY cu.coupon_id;

Upvotes: 2

Related Questions