©2020-2024 ioDraw All rights reserved
<>MySQL Statistics of total sales data of each month
Under the name ‘temp’ There is a sales table in the database (bb_sale), The structure is as follows
Field name type description remarks
F1Varchar sale ID
F2Varchar Sales date date format ：2014-02-14
F3Varchar sales amount
The data in the table are as follows
(1) Statistics 2014 year 1 to 12 month , Total sales data of each month , The format is as follows ： month , amount of money
SELECT b. month ,COALESCE(SUM(a.F3),0) AS amount of money FROM bb_sale a RIGHT JOIN ( SELECT
'2014-01' AS month FROM dual union SELECT '2014-02' AS month FROM dual union SELECT
'2014-03' AS month FROM dual union SELECT '2014-04' AS month FROM dual union SELECT
'2014-05' AS month FROM dual union SELECT '2014-06' AS month FROM dual union SELECT
'2014-07' AS month FROM dual union SELECT '2014-08' AS month FROM dual union SELECT
'2014-09' AS month FROM dual union SELECT '2014-10' AS month FROM dual union SELECT
'2014-11' AS month FROM dual union SELECT '2014-12' AS month FROM dual ) b ON
LEFT(a.F2,7)=b. month GROUP BY b. month
Operation effect :
(2) Statistics 2014 year 1 Month to 12 Total sales data of the month , The format is as follows ： month , amount of money . The months here are a little different , such as 1 Data of month , Refers to 1 month 15 No. to 2 month 15 Data between , Similarly 2 Data of month , Refers to 2 month 15 No. to 3 month 15 Data between , and so on .
SELECT '2014-01' AS month ,COALESCE((SELECT SUM(F3) FROM bb_sale WHERE
F2>='2014-01-15' AND F2 <'2014-02-15'),0) AS amount of money UNION SELECT '2014-02' AS
month ,COALESCE((SELECT SUM(F3) FROM bb_sale WHERE F2>='2014-02-15' AND F2
<'2014-03-15'),0) AS amount of money UNION SELECT '2014-03' AS month ,COALESCE((SELECT SUM(F3)
FROM bb_sale WHERE F2>='2014-03-15' AND F2 <'2014-04-15'),0) AS amount of money UNION SELECT
'2014-04' AS month ,COALESCE((SELECT SUM(F3) FROM bb_sale WHERE F2>='2014-04-15'
AND F2 <'2014-05-15'),0) AS amount of money UNION SELECT '2014-05' AS month ,COALESCE((SELECT
SUM(F3) FROM bb_sale WHERE F2>='2014-05-15' AND F2 <'2014-06-15'),0) AS amount of money
UNION SELECT '2014-06' AS month ,COALESCE((SELECT SUM(F3) FROM bb_sale WHERE
F2>='2014-06-15' AND F2 <'2014-07-15'),0) AS amount of money UNION SELECT '2014-07' AS
month ,COALESCE((SELECT SUM(F3) FROM bb_sale WHERE F2>='2014-07-15' AND F2
<'2014-08-15'),0) AS amount of money UNION SELECT '2014-08' AS month ,COALESCE((SELECT SUM(F3)
FROM bb_sale WHERE F2>='2014-08-15' AND F2 <'2014-09-15'),0) AS amount of money UNION SELECT
'2014-09' AS month ,COALESCE((SELECT SUM(F3) FROM bb_sale WHERE F2>='2014-09-15'
AND F2 <'2014-09-15'),0) AS amount of money UNION SELECT '2014-10' AS month ,COALESCE((SELECT
SUM(F3) FROM bb_sale WHERE F2>='2014-10-15' AND F2 <'2014-11-15'),0) AS amount of money
UNION SELECT '2014-11' AS month ,COALESCE((SELECT SUM(F3) FROM bb_sale WHERE
F2>='2014-11-15' AND F2 <'2014-12-15'),0) AS amount of money UNION SELECT '2014-12' AS
month ,COALESCE((SELECT SUM(F3) FROM bb_sale WHERE F2>='2014-12-15' AND F2
<='2014-12-31' or F2>='2014-01-01' AND F2<'2014-01-15'),0) AS amount of money
Operation effect :
Technology