CODEWITHSUNDEEP
mysql
Brian Powell
Brian Powell

Reputation: 3411

MySQL return month by month count for each distinct ID

I wrote the below query, which works, but it is incredibly inefficient and slow. What can I do to make it more efficient? I understand that it's basically "looping" over each ID in the system, there's about 500 of them, and running 13 select statements for each one - I just don't know a better way to get this information.

My date field is a DATE field stored as YYYY-MM-DD.

SELECT 
id as name,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-01-01' AND '2017-01-31') and id = name)  as January,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-02-01' AND '2017-02-31') and id = name)  as February,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-03-01' AND '2017-03-31') and id = name)  as March,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-04-01' AND '2017-04-31') and id = name)  as April,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-05-01' AND '2017-05-31') and id = name)  as May,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-06-01' AND '2017-06-31') and id = name)  as June,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-07-01' AND '2017-07-31') and id = name)  as July,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-08-01' AND '2017-08-31') and id = name)  as August,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-09-01' AND '2017-09-31') and id = name)  as September,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-10-01' AND '2017-10-31') and id = name)  as October,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-11-01' AND '2017-11-31') and id = name)  as November,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-12-01' AND '2017-12-31') and id = name)  as December,
 (SELECT sum(captured_profit) from me where (date BETWEEN '2017-11-01' AND '2017-12-31') and id = name)  as Total,
from me
where id is not null
group by id;

Upvotes: 2

Views: 258

Answers (4)

Larry Beasley
Larry Beasley

Reputation: 334

Bill Karwin beat me to it but I also added the sum rows grouped by year: 

SELECT 
    id as name,
    SUM(CASE WHEN MONTH(date) = 1 THEN captured_profit ELSE 0 END) AS January,
    SUM(CASE WHEN MONTH(date) = 2 THEN captured_profit ELSE 0 END) AS February,
    SUM(CASE WHEN MONTH(date) = 3 THEN captured_profit ELSE 0 END) AS March,
    SUM(CASE WHEN MONTH(date) = 4 THEN captured_profit ELSE 0 END) AS April,
    SUM(CASE WHEN MONTH(date) = 5 THEN captured_profit ELSE 0 END) AS May,
    SUM(CASE WHEN MONTH(date) = 6 THEN captured_profit ELSE 0 END) AS June,
    SUM(CASE WHEN MONTH(date) = 7 THEN captured_profit ELSE 0 END) AS July,
    SUM(CASE WHEN MONTH(date) = 8 THEN captured_profit ELSE 0 END) AS August,
    SUM(CASE WHEN MONTH(date) = 9 THEN captured_profit ELSE 0 END) AS September,
    SUM(CASE WHEN MONTH(date) = 10 THEN captured_profit ELSE 0 END) AS October,
    SUM(CASE WHEN MONTH(date) = 11 THEN captured_profit ELSE 0 END) AS November,
    SUM(CASE WHEN MONTH(date) = 12 THEN captured_profit ELSE 0 END) AS December,
    SUM(captured_profit) AS Total,
    YEAR(date)
from me
where id is not null
group by id, YEAR(date)
union
SELECT 
    NULL,
    SUM(CASE WHEN MONTH(date) = 1 THEN captured_profit ELSE 0 END) AS January,
    SUM(CASE WHEN MONTH(date) = 2 THEN captured_profit ELSE 0 END) AS February,
    SUM(CASE WHEN MONTH(date) = 3 THEN captured_profit ELSE 0 END) AS March,
    SUM(CASE WHEN MONTH(date) = 4 THEN captured_profit ELSE 0 END) AS April,
    SUM(CASE WHEN MONTH(date) = 5 THEN captured_profit ELSE 0 END) AS May,
    SUM(CASE WHEN MONTH(date) = 6 THEN captured_profit ELSE 0 END) AS June,
    SUM(CASE WHEN MONTH(date) = 7 THEN captured_profit ELSE 0 END) AS July,
    SUM(CASE WHEN MONTH(date) = 8 THEN captured_profit ELSE 0 END) AS August,
    SUM(CASE WHEN MONTH(date) = 9 THEN captured_profit ELSE 0 END) AS September,
    SUM(CASE WHEN MONTH(date) = 10 THEN captured_profit ELSE 0 END) AS October,
    SUM(CASE WHEN MONTH(date) = 11 THEN captured_profit ELSE 0 END) AS November,
    SUM(CASE WHEN MONTH(date) = 12 THEN captured_profit ELSE 0 END) AS December,
    SUM(captured_profit) AS Total,
    YEAR(date)
from me
where id is not null
group by YEAR(date)

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562260

Aggregate functions like SUM() ignore NULLs. So use an expression to make the profit value NULL unless it falls within the range you want.

SELECT 
  id AS name,
  SUM(CASE WHEN MONTH(date) = 1 THEN captured_profit END) AS January,
  SUM(CASE WHEN MONTH(date) = 2 THEN captured_profit END) AS February,
  SUM(CASE WHEN MONTH(date) = 3 THEN captured_profit END) AS March,
  SUM(CASE WHEN MONTH(date) = 4 THEN captured_profit END) AS April,
  SUM(CASE WHEN MONTH(date) = 5 THEN captured_profit END) AS May,
  SUM(CASE WHEN MONTH(date) = 6 THEN captured_profit END) AS June,
  SUM(CASE WHEN MONTH(date) = 7 THEN captured_profit END) AS July,
  SUM(CASE WHEN MONTH(date) = 8 THEN captured_profit END) AS August,
  SUM(CASE WHEN MONTH(date) = 9 THEN captured_profit END) AS September,
  SUM(CASE WHEN MONTH(date) = 10 THEN captured_profit END) AS October,
  SUM(CASE WHEN MONTH(date) = 11 THEN captured_profit END) AS November,
  SUM(CASE WHEN MONTH(date) = 12 THEN captured_profit END) AS December,
  SUM(captured_profit) AS Total
FROM me
WHERE id IS NOT NULL AND YEAR(date) = 2017
GROUP BY id;

The CASE expression returns NULL if there's no match and no ELSE clause. Read https://dev.mysql.com/doc/refman/5.7/en/case.html for more details.

This query eliminates all the correlated subqueries. The query will scan through the table just once.

I don't know what the meaning of your "Total" is in your query, but it appears to be just November + December. I'm not sure if that's what you intended.

Edit: I fixed the expression for Total.

Upvotes: 1

baao
baao

Reputation: 73221

Simply group by year, month and id

select id, sum(captured_profit), month( `date`)
from me 
where id is not null and `date` > '2016-12-31'
group by year(`date`), month(`date`), id;

I guess you didn't use all the keywords (name, date) as your actual column names, if you did make sure to put them in backticks in every query.

Upvotes: 1

Sulyman
Sulyman

Reputation: 450

I think this is exactly what you are looking for

http://www.w3resource.com/mysql/date-and-time-functions/mysql-monthname-function.php

MySQL MONTHNAME() returns the full name of the month for a given date. The return value is within the range of 1 to 12 ( January to December). It Returns NULL when month part for the date is 0 or more than 12

Syntax :

MONTHNAME(date1)

Upvotes: 0

Related Questions