CODEWITHSUNDEEP
sqlsql-servert-sql
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Count numbers that don't match between 2 tables

My original query is to count by month how many items have never been ordered.

I have created a sql fiddle here http://sqlfiddle.com/#!18/e89a7/2

And put together the query below

SELECT COUNT(ItemNo)
FROM Item
WHERE ItemNo NOT IN 
(SELECT ItemNo
FROM Order1)

However this doesn't do what I want and I wonder if I'm going about it the wrong way. I need this to be grouped by ordered month and year but the NOT IN clause doesn't allow me to do this.

Sample data:

CREATE TABLE Item (
    ItemNo varchar(10),
    MonthStocked varchar(10),
    YearStocked varchar(10)
);

CREATE TABLE Order1 (
    OrderNo int,
    ItemNo varchar(10),
    MonthOrdered varchar(10),
    YearOrdered varchar(10)
);

INSERT INTO Item (ItemNo, MonthStocked,YearStocked)
VALUES ('111','Feb', 2017),
('222','Jan',  2018),
('333','Feb', 2017),
('444','Feb', 2017),
('555','Jan', 2017),
('666','Jan', 2017);

INSERT INTO Order1 (OrderNo, ItemNo,MonthOrdered,YearOrdered)
VALUES ('897', '111', 'Dec', '2017'),
('657', '222', 'Nov', '2017'),
('896', '333', 'Nov' , '2017'),
('867', '333', 'Dec' , '2017'),
('234', '444', 'Nov' , '2017');

desired output:

| ItemsNotOrdered  |     Month    |     Year    |
|------------------|--------------|-------------|
|                3 |          Nov |        2017 |
|                4 |          Dec |        2017 |

Upvotes: 1

Views: 86

Answers (4)

paparazzo
paparazzo

Reputation: 45096

I don't like your desired output as it misses months where nothing is sold. I also don't like that months don't sort.

declare @item table (ItemNo varchar(10), MonthStocked varchar(10), YearStocked varchar(10))
INSERT INTO @Item (ItemNo, MonthStocked,YearStocked)
VALUES ('111','Feb', 2017),
       ('222','Jan', 2018),
       ('333','Feb', 2017),
       ('444','Feb', 2017),
       ('555','Jan', 2017),
       ('666','Jan', 2017);

declare @Order1 table (OrderNo int, ItemNo varchar(10), MonthOrdered varchar(10), YearOrdered varchar(10)) 
INSERT INTO @Order1 (OrderNo, ItemNo,MonthOrdered,YearOrdered)
VALUES ('897', '111', 'Dec', '2017'),
       ('657', '222', 'Nov', '2017'),
       ('896', '333', 'Nov', '2017'),
       ('867', '333', 'Dec', '2017'),
       ('234', '444', 'Nov', '2017');

declare @itemCount int = (select count(distinct ItemNo) from @item);

with CTEyymm as 
( select MonthStocked as mm, YearStocked as yy
    from @item 
  union
  select MonthOrdered, YearOrdered
    from @Order1
)

select yymm.yy, yymm.mm
     , (@itemCount - count(distinct(o.ItemNo))) as cnt  
  from CTEyymm yymm 
  left join @order1 o 
    on o.YearOrdered  = yymm.yy 
   and o.MonthOrdered = yymm.mm 
 group by yymm.yy, yymm.mm
 order by yymm.yy, yymm.mm;

If you don't care about blank months then just: 

select o.YearOrdered, o.MonthOrdered
     , (@itemCount - count(distinct(o.ItemNo))) as cnt  
  from @order1 o 
 group by o.YearOrdered, o.MonthOrdered
 order by o.YearOrdered, o.MonthOrdered

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

Another option using OUTER APPLY 

SELECT   DISTINCT 
         MonthOrdered,
         YearOrdered,
         ItemsNotOrdered
FROM     Order1 o
OUTER APPLY (
         SELECT COUNT(ItemNo) ItemsNotOrdered
         FROM   Item i
         WHERE  NOT EXISTS (
            SELECT  ItemNo 
            FROM    Order1 o2
            WHERE   i.ItemNo = o2.ItemNo
            AND     o2.MonthOrdered = o.MonthOrdered
            AND     o2.YearOrdered = o.YearOrdered
         ) 
) ino

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270723

You can use cross join to generate all the item/year/month combinations. Then left join and group by to get the results you want:

select ym.mon, ym.mon, count(*)
from item i cross join
     (select distinct MonthOrdered as mon, YearOrdered as yr from order1
     ) ym left join
     order1 o
     on i.itemno = o.itemno and ym.mon = o.MonthOrdered and ym.yr = o.YearOrdered 
where o.itemno is null
group by ym.yr, ym.mon;

Here is the SQL Fiddle.

Upvotes: 1

John Woo
John Woo

Reputation: 263843

You need to get the cartesian product using CROSS JOIN between all unique ItemNo to all unique ordered months and years. The resulting product will then be outer join to table Items on three conditions. The inexisting ItemNo are simply the one you are looking for.

SELECT  b.MonthOrdered, b.YearOrdered,
        COUNT(CASE WHEN c.ItemNo IS NULL THEN 1 END)
FROM    (SELECT DISTINCT ItemNo FROM Item) a
        CROSS JOIN (SELECT  DISTINCT MonthOrdered, YearOrdered FROM Order1) b
        LEFT JOIN Order1 c
            ON a.ItemNo = c.ItemNo
                AND b.MonthOrdered = c.MonthOrdered 
                AND b.YearOrdered = c.YearOrdered 
 GROUP BY b.MonthOrdered, b.YearOrdered

Here's a Demo.

If ItemNo is unique, eliminate the subquery and use the table directly.

SELECT  b.MonthOrdered, b.YearOrdered,
        COUNT(CASE WHEN c.ItemNo IS NULL THEN 1 END)
FROM    Item a
        CROSS JOIN (SELECT  DISTINCT MonthOrdered, YearOrdered FROM Order1) b
        LEFT JOIN Order1 c
            ON a.ItemNo = c.ItemNo
                AND b.MonthOrdered = c.MonthOrdered 
                AND b.YearOrdered = c.YearOrdered 
 GROUP BY b.MonthOrdered, b.YearOrdered

Here's a Demo.

Upvotes: 2

Related Questions