当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Get product total sales per moth, with 0 in the gaps

问题描述:

I have been stuck in a recent problem with a SQL Query. What I'm trying to archieve is to get each product in the store and show how many of them has been sold each month. However, sometimes there are some months where these products were not sold, which means they won't be displayed.

For instance, this is the result I'm getting right now

 Article Month Sold

CN140027 6 312

CN140027 7 293

CN140027 12 122

CN140186 1 10

CN140186 4 2

While I want to get something more like this

 Article Month Sold

CN140027 6 312

CN140027 7 293

CN140027 8 0

CN140027 9 0

CN140027 10 0

CN140027 11 0

CN140027 12 122

CN140186 1 10

CN140186 2 0

CN140186 3 0

CN140186 4 2

And here is the query I'm using at the moment

SELECT k.artikelnr, Months.datefield as `Months`, IFNULL(SUM(k.menge),0) as `Quantity`

FROM store_shop_korb as k LEFT OUTER JOIN office_calendar AS Months

ON Months.datefield = month(k.date_insert)

WHERE k.date_insert BETWEEN "2014-12-01" AND "2015-12-31"

group by k.artikelnr, Months.datefield

What am I missing? Or what am I doing wrong? Any help is really appreciated.

Thanks in advance.

EDIT:

Additional information:

office_calendar is the calendar table. It only contains the months as registry, from 1 to 12.

Additionally, I'm taking the article/product ID from a table called 'store_shop_korb', which contains all the lines of a made order (so it contains the article ID, its price, the quantity for each order..)

网友答案:

This works for me:

SELECT k.artikelnr, c.datefield AS `Month`, COALESCE(s.Quantity, 0) AS Sold
FROM (
    SELECT artikelnr
    FROM store_shop_korb
    GROUP BY artikelnr
) k
JOIN office_calendar c
LEFT JOIN (
    SELECT artikelnr, MONTH(date_insert) AS monthfield, SUM(menge) AS Quantity
    FROM store_shop_korb
    GROUP BY artikelnr, MONTH(date_insert)
) s ON k.artikelnr = s.artikelnr AND c.datefield = s.monthfield
ORDER BY k.artikelnr, c.datefield

If you have a table of articles, you can use it in the place of subquery k. I'm basically normalizing on the fly.

Explanation:

There's basically 3 sets of data that get joined. The first is a distinct set of articles (k), the second is a distinct set of months (c). These two are joined without restriction, meaning you get the cartesian product (every article x every month). This result is then left-joined to the sales per month (s) so that we don't lose 0 entries.

网友答案:

Add another where condition , i think it will solve your problem

SELECT k.artikelnr, Months.datefield as `Months`, IFNULL(SUM(k.menge),0) as `Quantity`

FROM store_shop_korb as k LEFT OUTER JOIN office_calendar AS Months ON Months.datefield = month(k.date_insert) WHERE IFNULL(SUM(k.menge),0)>0 AND k.date_insert BETWEEN "2014-12-01" AND "2015-12-31" group by k.artikelnr, Months.datefield

网友答案:

I have tried this in MSAccess and it seems to work OK

SELECT PRODUCT, CALENDAR.MONTH, A

FROM CALENDAR LEFT JOIN (

    SELECT PRODUCT, MONTH(SALEDTE) AS M, SUM(SALEAMOUNT) AS A
    FROM SALES 
    WHERE SALEDTE BETWEEN #1/1/2015# AND #12/31/2015#
    GROUP BY PRODUCT, MONTH(SALEDTE) ) AS X

ON X.M = CALENDAR.MONTH

网友答案:

If you already have a calender table then use this.

SELECT B.Article,
       A.Month,
       COALESCE(c.Sold, 0)
FROM   (SELECT DISTINCT Months.datefield --Considering this as months feild
        FROM   office_calendar AS Months) A
       CROSS JOIN (SELECT DISTINCT article
                   FROM   Yourtable) B
       LEFT OUTER JOIN Yourtable C
                    ON a.month = c.Month
                       AND b.Article = c.Article 

Else you need a months table. Try this.

SELECT *
FROM   (SELECT 1 AS month UNION
        SELECT 2 UNION
        SELECT 3 UNION
        SELECT 4 UNION
        SELECT 5 UNION
        SELECT 6 UNION
        SELECT 7 UNION
        SELECT 8 UNION
        SELECT 9 UNION
        SELECT 10 UNION
        SELECT 11 UNION
        SELECT 12) A
       CROSS JOIN (SELECT DISTINCT article
                   FROM   Yourtable) B
       LEFT OUTER JOIN Yourtable C
                    ON a.month = c.Month
                       AND b.Article = c.Article 
分享给朋友:
您可能感兴趣的文章:
随机阅读: