CODEWITHSUNDEEP
sql
Joe Kuek
Joe Kuek

Reputation: 3

Grouping different results from same table

I would like to get a result from a table:

Date    Charges

22/04/2010  1764  
22/04/2010  200  
22/04/2010  761  
22/04/2010  3985  
22/04/2010  473  
22/04/2010  677  
22/04/2010  1361  
22/04/2010  6232  
22/04/2010  4095  
23/04/2010  7224  
23/04/2010  1748  
23/04/2010  1355  
23/04/2010  2095  
23/04/2010  2063   
23/04/2010  2331  
23/04/2010  2331  
23/04/2010  4473  
23/04/2010  478  
23/04/2010  1901  
23/04/2010  1250  
23/04/2010  1743  
24/04/2010  1743  
24/04/2010  3923  
24/04/2010  1575   
24/04/2010  1859  
24/04/2010  2431   
24/04/2010  1208  
24/04/2010  158  
24/04/2010  3246  
24/04/2010  2898  
24/04/2010  1517  
24/04/2010  2368  
24/04/2010  961  
24/04/2010  4111  
24/04/2010  3066  
24/04/2010  740  
25/04/2010  2651  
25/04/2010  2693  
25/04/2010  4847  
25/04/2010  312  
25/04/2010  1247  
25/04/2010  5858  
25/04/2010  1040  
25/04/2010  941  
25/04/2010  942  
25/04/2010  1784  
25/04/2010  418  
25/04/2010  2248  
25/04/2010  1834  
25/04/2010  418  
25/04/2010  2263  
26/04/2010  2746  
26/04/2010  942  
26/04/2010  883  
26/04/2010  3339  
26/04/2010  3517  
26/04/2010  761  
26/04/2010  1738  
26/04/2010  1370  
26/04/2010  1501  
26/04/2010  1197  
26/04/2010  2452  
26/04/2010  209  
26/04/2010  1092  
26/04/2010  4316  
26/04/2010  1208  
26/04/2010  1213  
26/04/2010  2179  
26/04/2010  1213  
26/04/2010  1538  
26/04/2010  1939  
26/04/2010  956  
26/04/2010  10715  
26/04/2010  4321  
26/04/2010  956  
26/04/2010  2975  
26/04/2010  798  
26/04/2010  1738

where it shows the following fields:

Date, Count of >2500, Total of >2500, Total Count and Grand total between 1/4/2010 to 30/4/2010

i.e.
22/4/2010, 3, 14312, 9, 19548  
23/4/2010, 2, 11697, 12, 28992  
24/4/2010, 5, 17244, 15, 31804  
25/4/2010, 4, 16049, 15, 29496  
26/4/2010, 7, 31929, 27, 57812

...
...

All help are much appreciated! Thanks in advance.

Upvotes: 0

Views: 193

Answers (2)

Borja
Borja

Reputation: 2198

You can try with:

SELECT date,
       count(if(charges>2500, 1, NULL)) as countGt2500,
       sum(if(charges>2500, charges, 0)) as totalGt2500,
       count(charges) as countTotal,
       sum(charges) as sumTotal,
  FROM yourTable
 WHERE date >= '2010/04/01'
   AND date <= '2010/04/30'
GROUP BY date;

If you saved the full datetime on the field date you have to extract the date part from the datetime, to do it you can use the DATE function on the following way:

SELECT DATE(date) as day,
       count(if(charges>2500, 1, NULL)) as countGt2500,
       sum(if(charges>2500, charges, 0)) as totalGt2500,
       count(charges) as countTotal,
       sum(charges) as sumTotal,
  FROM yourTable
 WHERE date >= '2010/04/01'
   AND date <= '2010/04/30'
GROUP BY day;

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

Basics would be to use SUM and CASE, something like:

SELECT
    DATEADD(day,DATEDIFF(day,'20010101',DateTimeActivity),'20010101') as Date,
    SUM(CASE WHEN Charges > 2500 THEN 1 ELSE 0 END) as Count2500,
    SUM(CASE WHEN Charges > 2500 THEN Charges END) as Sum2500,
    COUNT(*) as CountTotal,
    SUM(Charges) as SumTotal
FROM
    AccActivity
WHERE
    DateTimeActivity >= '20100401' and
    DateTimeActivity < '20100501'
GROUP BY
    DATEADD(day,DATEDIFF(day,'20010101',DateTimeActivity),'20010101')

Updated based on your comment, to use real table/column names. I assume you want to include transactions which occur on 30th April.

Note that I'm using a safe date format for my date literals (YYYYMMDD) - most other formats are ambiguous based on the regional settings on the server.

Also, I'm using DATEADD(day,DATEDIFF(day,'20010101',DateTimeActivity),'20010101') to strip the time component from the datetime - it looks slightly funky, but it's reasonable fast, and the same pattern can be used to do other datetime conversions relatively easily (e.g. if you need to group on months, you can just change both day options to month, and the dates will all be set to the 1st of their respective month)

Upvotes: 2

Related Questions