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?

Recommended Answers

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 …

Jump to Post

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 …
Jump to Post

All 11 Replies

Member Avatar

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.

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

Member Avatar

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

Thanks for help !!! Many many thanks !

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?

Member Avatar

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

Member Avatar

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.

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

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
Member Avatar

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.

Be a part of the DaniWeb community

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