CODEWITHSUNDEEP
mysqlsqlcount
jlivni
jlivni

Reputation: 4779

Get count summaries for each result for the last week

I currently have a list of the top X results for a given table, for a given day, where 'top' is defined as those results with the highest aggregate count. What I want to be able to see is not just the count for that particular day, but also how it compares to yesterday's count, and the average of last week's counts.

Here is my current query:

SELECT foo, bar, SUM(count) as today
FROM tablename
WHERE col1 = 'asdf' and date = '2012-08-23'
GROUP BY foo, bar
ORDER BY count desc
LIMIT 5

The result looks something like:

|foo        | bar        | today |
|something1 | something3 | 2345  |
|something2 | something4 | 1234  |
 ....

But what I want to have is something like this:

|foo        | bar        | today | yesterday | week_avg |
|something1 | something3 | 2345  | 2273      | 2193     |
|something2 | something4 | 1234  | 935       | 1001     |
 ....

As bonus points I wouldn't mind having all the dates for the last 20 days in some kind of GROUP_CONCAT result (so I could easily make a sparkline with it)

Upvotes: 0

Views: 355

Answers (3)

Zane Bien
Zane Bien

Reputation: 23125

You can get all rows from the last week, then use conditional aggregation (via CASE expressions) to only sum the count values of the current day and the count values of yesterday:

SELECT   foo, 
         bar,
         SUM(CASE WHEN date = CURDATE() THEN count ELSE 0 END) AS today,
         SUM(CASE WHEN date = CURDATE() - INTERVAL 1 DAY THEN count ELSE 0 END) AS yesterday,
         AVG(count) AS week_avg
FROM     tablename
WHERE    col1  = 'asdf' AND
         date >= CURDATE() - INTERVAL 1 WEEK
GROUP BY foo,
         bar
ORDER BY today DESC
LIMIT    5

Finally, we order by the sum of today's count values and select the top five based on that order.

Upvotes: 3

O. Jones
O. Jones

Reputation: 108641

Your first question. Try this:

 SELECT day0.foo, day0.bar, 
        day0.addup as today, 
        day1.addup as yesterday, 
        week0.addup/7 as week_avg
   FROM 
  ( 
    SELECT foo, bar, SUM(count) as addup
      FROM tablename
     WHERE col1 = 'asdf'
       AND date >= CURDATE()
       AND date < CURDATE() + INTERVAL 1 DAY
  GROUP BY foo, bar
  ) day0
  JOIN ( 
    SELECT foo, bar, SUM(count) as addup
      FROM tablename
     WHERE col1 = 'asdf'
       AND date >= CURDATE() - INTERVAL 1 DAY
       AND date < CURDATE()
  GROUP BY foo, bar
  ) day1 ON (day0.foo = day1.foo AND day0.bar=day1.bar)
  JOIN ( 
    SELECT foo, bar, SUM(count) as addup
      FROM tablename
     WHERE col1 = 'asdf'
       AND date >= CURDATE() - INTERVAL 7 DAY
       AND date < CURDATE()
  GROUP BY foo, bar
  ) week0 ON (day0.foo = week0.foo AND day0.bar = week0.bar)
 ORDER BY day0.addup desc
    LIMIT 5

It looks hairy, but it's the join of three different summary queries, one for each day.

Your second question:

SELECT foo, bar, GROUP_CONCAT(addup)
  FROM (
    SELECT foo, bar, 
           DATEDIFF(CURDATE(),date) AS daysago, 
           SUM(count) as addup
      FROM tablename
     WHERE col1 = 'asdf'
       AND date >= CURDATE() - INTERVAL 20 DAY
       AND date < CURDATE() )A
  GROUP BY foo, bar, date
  ORDER BY foo, bar, date

Upvotes: 0

Kermit
Kermit

Reputation: 34055

Once you've prepared your summary table with the date and the date's amount, and assuming you are looking for the difference from yesterday, you can use COALESCE. The tbl table below refers to your summary table which would look something like:

SELECT date, foo, bar, SUM(count) as today
FROM tablename
WHERE col1 = 'asdf'
GROUP BY date, foo, bar
ORDER BY count desc
LIMIT 5

The query

SELECT a.ddate, a.amount AS today, 
COALESCE(a.amount - 
  (SELECT b.amount 
   FROM tbl b 
   WHERE b.ddate = a.ddate - INTERVAL 1 DAY), a.amount) AS yesterday
FROM tbl a

See the fiddle.

Upvotes: 0

Related Questions