How to select from mysql that group by month in one year

Question

Lau_1 12 Junior Poster in Training

9 Years Ago

Hello, please help me on this. I want to select from these table,

table name: User

| Register_date |
 ---------------
| 2015-02-04    |
| 2015-02-04    |
| 2015-02-14    |
| 2015-03-14    |
| 2015-04-04    |
| 2015-04-14    |


table name: Reservation

| Order_date | Amount |
 ----------- | ------ |
| 2015-02-13 | 24.15  |
| 2015-02-13 | 12.00  |
| 2015-02-16 | 14.12  |
| 2015-03-02 | 17.01  |
| 2015-03-14 | 18.25  |
| 2015-04-14 | 19.24  |

And i want to create a table like this for whole year

| Year | Month | Total_Register | Total_order | Total_Income |
| ---- | ----- | -------------- | ----------- | ------------ |
| 2015 | 01    | 0              | 0           | 0            |  
| 2015 | 02    | 3              | 3           | 50.27        |  
| 2015 | 03    | 1              | 2           | 35.26        |  
| 2015 | 04    | 2              | 1           | 19.24        |  
| 2015 | 05    | 0              | 0           | 0            |  
| 2015 | 06    | 0              | 0           | 0            |  
| 2015 | 07    | 0              | 0           | 0            | 
| 2015 | 08    | 0              | 0           | 0            | 
| 2015 | 09    | 0              | 0           | 0            | 
| 2015 | 10    | 0              | 0           | 0            |   
| 2015 | 11    | 0              | 0           | 0            |   
| 2015 | 12    | 0              | 0           | 0            |

How should i do?

mysql

Edited 9 Years Ago by Lau_1

3 Contributors
11 Replies
29K Views
2 Days Discussion Span
Latest Post 9 Years Ago Latest Post by diafol

minitauros 151 Junior Poster

9 Years Ago

Well, a quick search on Google seems to result in an answer to your question.

See http://stackoverflow.com/questions/3366895/group-by-month-and-year-in-mysql

diafol

9 Years Ago

Thanks m. I feel a bit rotten for flashing a solution then pulling it, so here's a pseudosolution (part of):

This is for the derived table to be used with the main query - which will use MAX(firstcount) etc. THe main query will also have to group by the year and month fields.

(SELECT YEAR(date1) AS yr, MONTH(date1) AS m, COUNT(date1) AS firstcount, 0 AS secondcount, 0 AS firstsum
FROM registrations
GROUP BY YEAR(date1), MONTH(date1)
UNION
SELECT YEAR(date2) AS yr, MONTH(date2) AS m, 0 AS firstcount, COUNT(date2) AS secondcount, SUM(price) AS firstsum
FROM orders
GROUP BY YEAR(date2), MONTH(date2)) AS derivedtable

diafol

9 Years Ago

Sorry, was out:

SELECT yr AS `Year`, mnth AS `Month`, MAX(regcount) AS `Registrations`, MAX(ordcount) AS `Orders`, MAX(pricetotal) AS `Total Orders`
FROM
(SELECT YEAR(regdate) AS yr, MONTH(regdate) AS mnth, COUNT(regdate) AS regcount, 0 AS ordcount, 0 AS pricetotal
FROM registrations
GROUP BY YEAR(regdate), MONTH(regdate)
UNION
SELECT YEAR(orderdate) AS yr, MONTH(orderdate) AS mnth, 0 AS regcount, COUNT(orderdate) AS ordcount, SUM(price) AS pricetotal
FROM orders
GROUP BY YEAR(orderdate), MONTH(orderdate)) AS derivedtable
GROUP BY yr, mnth
ORDER BY yr, mnth

Bit of a fudge with creating false columns, but it works for me.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

diafol · Answer 1 · 2015-06-24T10:15:10+00:00

THe link from minitauros will give you the GROUP BY clause, but you will use the aggregate functions, COUNT and SUM and find a way to combine the two tables. I'm sure there's a way to do it with a join, but I gave it a go and I was able to do it easily(?) with a UNION and MAX:

//EDIT

Sorry deleted solution as I realised that you haven't shown any code yourself yet. Apologies.

minitauros 151 Junior Poster Featured Poster · Answer 2 · 2015-06-24T11:34:31+00:00

Sorry, forgot to explain that other bit. Thanks Diafol! Nice addition.

Lau_1 12 Junior Poster in Training · Answer 3 · 2015-06-25T01:24:44+00:00

Lau_1 12 Junior Poster in Training

9 Years Ago

Thanks for help !!! Many many thanks !

Lau_1 12 Junior Poster in Training · Answer 4 · 2015-06-25T02:13:34+00:00

Sorry to ask again, i try to display the month which are not in the table. By using this method:

SELECT YEAR(m.merge_date) AS Year, DATE_FORMAT(merge_date,'%b') AS Month,  COUNT(date) AS Total_Register_User, 0 AS Total_Order, 0 AS Total_Income
FROM (
           SELECT '2015-01-01' AS merge_date
           UNION SELECT '2015-02-01' AS merge_date
           UNION SELECT '2015-03-01' AS merge_date
           UNION SELECT '2015-04-01' AS merge_date
           UNION SELECT '2015-05-01' AS merge_date
           UNION SELECT '2015-06-01' AS merge_date
           UNION SELECT '2015-07-01' AS merge_date
           UNION SELECT '2015-08-01' AS merge_date
           UNION SELECT '2015-09-01' AS merge_date
           UNION SELECT '2015-10-01' AS merge_date
           UNION SELECT '2015-11-01' AS merge_date
           UNION SELECT '2015-12-01' AS merge_date
          ) AS m

          LEFT JOIN users u ON MONTH(m.merge_date) = MONTH(u.date) AND YEAR(m.merge_date) = YEAR(u.date)
GROUP BY m.merge_date
UNION
SELECT YEAR(m.merge_date) AS Year, DATE_FORMAT(merge_date,'%b') AS Month,  0 AS Total_Register_User, COUNT(date) AS Total_Order, SUM(amount) AS Total_Income
FROM (
           SELECT '2015-01-01' AS merge_date
           UNION SELECT '2015-02-01' AS merge_date
           UNION SELECT '2015-03-01' AS merge_date
           UNION SELECT '2015-04-01' AS merge_date
           UNION SELECT '2015-05-01' AS merge_date
           UNION SELECT '2015-06-01' AS merge_date
           UNION SELECT '2015-07-01' AS merge_date
           UNION SELECT '2015-08-01' AS merge_date
           UNION SELECT '2015-09-01' AS merge_date
           UNION SELECT '2015-10-01' AS merge_date
           UNION SELECT '2015-11-01' AS merge_date
           UNION SELECT '2015-12-01' AS merge_date
          ) AS m

          LEFT JOIN reservation r ON MONTH(m.merge_date) = MONTH(r.date) AND YEAR(m.merge_date) = YEAR(r.date)
GROUP BY m.merge_date

and the result duplicated, hmmm, what change should do?

diafol · Answer 5 · 2015-06-25T07:04:04+00:00

Ok give me 30 minutes. Am busy. Will post what I have

Lau_1 12 Junior Poster in Training · Answer 6 · 2015-06-26T00:38:08+00:00

Sorry to say, query result was same as the previous. Actually I want to add row, which doesnt exist in the table, to get the result as below

| Year | Month | Total_Register | Total_order | Total_Income |
| ---- | ----- | -------------- | ----------- | ------------ |
| 2015 | 01    | 0              | 0           | 0            |  
| 2015 | 02    | 3              | 3           | 50.27        |  
| 2015 | 03    | 1              | 2           | 35.26        |  
| 2015 | 04    | 2              | 1           | 19.24        |  
| 2015 | 05    | 0              | 0           | 0            |  
| 2015 | 06    | 0              | 0           | 0            |  
| 2015 | 07    | 0              | 0           | 0            | 
| 2015 | 08    | 0              | 0           | 0            | 
| 2015 | 09    | 0              | 0           | 0            | 
| 2015 | 10    | 0              | 0           | 0            |   
| 2015 | 11    | 0              | 0           | 0            |   
| 2015 | 12    | 0              | 0           | 0            |

For jan, may, june, july... which doesnt exist in the table, but I want to include in the query result. Sorry for my stupid ya

Lau_1 12 Junior Poster in Training · Answer 7 · 2015-06-26T01:03:40+00:00

Hello ! I fixed it, but thanks for ur help! Thank you very much !

SELECT YEAR(m.merge_date) AS Year, DATE_FORMAT(merge_date,'%b') AS Month,  COUNT(u.date) AS Total_Register_User, COUNT(r.date) AS Total_Order, SUM(IFNULL(r.amount,0)) AS Total_Income
FROM (
           SELECT '2015-01-01' AS merge_date
           UNION SELECT '2015-02-01' AS merge_date
           UNION SELECT '2015-03-01' AS merge_date
           UNION SELECT '2015-04-01' AS merge_date
           UNION SELECT '2015-05-01' AS merge_date
           UNION SELECT '2015-06-01' AS merge_date
           UNION SELECT '2015-07-01' AS merge_date
           UNION SELECT '2015-08-01' AS merge_date
           UNION SELECT '2015-09-01' AS merge_date
           UNION SELECT '2015-10-01' AS merge_date
           UNION SELECT '2015-11-01' AS merge_date
           UNION SELECT '2015-12-01' AS merge_date
          ) AS m

LEFT JOIN users u ON MONTH(m.merge_date) = MONTH(u.date) AND YEAR(m.merge_date) = YEAR(u.date)
LEFT JOIN reservation r ON MONTH(m.merge_date) = MONTH(r.date) AND YEAR(m.merge_date) = YEAR(r.date)
GROUP BY m.merge_date
ORDER BY 1+1

diafol · Answer 8 · 2015-06-26T09:12:16+00:00

Hi Lau, saw this is 'solved' and I missed the line about providing empty rows. However, I can't help feeling that this 'solution' will be very difficult to maintain since the rows have to be 'hard-coded'. I'll have a think. Perhaps this can be avoided.

//EDIT

SOrry the only way I could see of doing it was with a PHP script.