Grouping date ranges per row
I'm trying to output the daily, weekly, monthly and years count of orders and the sum amount of those orders between those time ranges. At the moment I have come up with the following. Its long, but its just a copy and paste job of the same statement just amended slightly to group by the year, month, week and day.
SELECT YEAR(s.LastUpdated) AS 'Year', 0 AS 'Month', 0 AS 'Week', 0 AS 'Day', count(rowId) AS 'Transactions', ROUND(SUM(Amount),2) AS 'Partner Cut'
FROM gfw_orders AS o
JOIN gfw_sage_orders AS s
ON o.sageRef = s.VendorTxCode
WHERE YEAR(s.LastUpdated) = 2011
GROUP BY
YEAR(s.LastUpdated)
UNION
SELECT YEAR(s.LastUpdated) AS 'Year', MONTHNAME(s.LastUpdated) AS 'Month', 0 AS 'Week', 0 AS 'Day', count(rowId) AS 'Transactions', ROUND(SUM(Amount),2) AS 'Partner Cut'
FROM gfw_orders AS o
JOIN gfw_sage_orders AS s
ON o.sageRef = s.VendorTxCode
WHERE MONTH(s.LastUpdated) = 1
GROUP BY
YEAR(s.LastUpdated), MONTH(s.LastUpdated)
UNION
SELECT YEAR(s.LastUpdated) AS 'Year', MONTHNAME(s.LastUpdated) AS 'Month', WEEKOFYEAR(s.LastUpdated) AS 'Week', 0 AS 'Day', count(rowId) AS 'Transactions', ROUND(SUM(Amount),2) AS 'Partner Cut'
FROM gfw_orders AS o
JOIN gfw_sage_orders AS s
ON o.sageRef = s.VendorTxCode
WHERE WEEK(s.lastUpdated) = 4
GROUP BY
YEAR(s.LastUpdated), MONTH(s.LastUpdated), WEEK(s.LastUpdated)
UNION
SELECT YEAR(s.LastUpdated) AS 'Year', MONTHNAME(s.LastUpdated) AS 'Month', WEEKOFYEAR(s.LastUpdated) AS 'Week', DAY(s.lastUpdated), count(rowId) AS 'Transactions', ROUND(SUM(Amount),2) AS 'Partner Cut开发者_如何学运维'
FROM gfw_orders AS o
JOIN gfw_sage_orders AS s
ON o.sageRef = s.VendorTxCode
WHERE DAY(s.LastUpdated) = 26
GROUP BY
YEAR(s.LastUpdated), MONTH(s.LastUpdated), WEEK(s.LastUpdated), DAY(s.LastUpdated)
Which outputs
Year Month Week Day Count Amount
2011 0 0 0 3 285.00
2011 January 0 0 3 285.00
2011 January 4 0 2 190.00
2011 January 4 26 1 95.00
Which is a ideal output providing the year, month, week, daily count and transactions sum which can be outputted accordingly. However that's for only one type of order. In the orders table there's two types of orders, online and terminal and ideally I'd like to output both types in the same query instead of query the database multiple times.
I have two questions which I'm having issues with. Firstly, is there a simpler way to achieve the same result? I don't wish to over complicate it because leading onto the second question, because some orders differ and I'd like the result to show both online orders and terminal orders if I simply copy and UNION the code I already have with the added 'WHERE Terminal = 1' because of the union the results are the same. Is there a way to output two sets of outputs in the same query?
I'm probably over complicating what I'm aiming for, but thank you in advance for any advice or pointers.
can you try like this example by using GROUP BY WITH ROLLUP
mysql> SELECT year, country, product, SUM(profit)
-> FROM sales
-> GROUP BY year, country, product WITH ROLLUP;
Result will be
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+------------+-------------+
For Rerence with ROLLUP
Your query is pretty big that's why i choose to give another example!
精彩评论