0

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?

Edited by Lau_1

3
Contributors
11
Replies
65
Views
2 Years
Discussion Span
Last Post by diafol
Featured Replies
  • 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 Read More

  • 2
    diafol 3,720   2 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 … Read More

  • 1
    diafol 3,720   2 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 … Read More

  • 1
    diafol 3,720   2 Years Ago

    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 … Read More

0

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.

Edited by diafol

2

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
0

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?

0

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

1

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.

0

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

Edited by Lau_1

0

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
1

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.

Edited by diafol

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.