问题描述:

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
```