How to sum up unique values using case statements having certain conditions

Question

I have a table that may have the same item but with different sizes, I would like to count those items with more than one size (e.g. marvel shirt with S, M sizes will count as "1"), but still be able to count how many S and M. I have 2 outcomes I would like to get. Please see below for more detail.

TABLE B   

ITEM_NO ITEM             
=========================
3130C   MARVEL_SHIRT     
1845C   SPONGEBOB_BOXERS 
A900C   CK_COAT          
A988C   RIDER_JEANS      


TABLE C

ITEM_NO SIZE          
===============
3130C   S             
3130C   M             
1845C   M             
A900C   L             
A988C   M     -

I tried just counting it but it is incorrect since it counts how many distinct sizes are there

select (case substr(item_no, 5, 1)
            when 'C' then 'clothes'
            when 'T' then 'toys'
            else 'misc' 
        end) inv, 
       count(item_no) total 
       ,sum (case when C.size = 'S' then 1 else 0 end) AS small
       ,sum (case when C.size = 'M' then 1 else 0 end) AS med
       ,sum (case when C.size = 'L' then 1 else 0 end) AS large   
       ,count (distinct C.size) AS multiple_sizes
        from B left outer join C on B.item_no = C.item_no 
        group by substr(item_no, 5, 1);

Actual outcome (incorrect):

INV     TOTAL   Small   Med   Large   Multiple_Sizes
==========================================================
CLOTHES    4       1    3       1       3

Desired/expected outcome:

INV     TOTAL   Small   Med   Large   Multiple_Sizes
==========================================================
CLOTHES    4       1    3       1       1

Below is another possible desired outcome given this scenario: What if those with multiple sizes shouldn't be counted separately (i.e. Marvel shirt has multiple sizes, thus it won't count the S or M since it's already counted under Multiple_Sizes)?

INV     TOTAL   Small   Med   Large     Multiple_Sizes
==========================================================
CLOTHES    4       0    2     1        1

How come Multiple_Size = 3 is incorrect ? isn't S, M and L part of multiple sizes ? — Avi, Feb 06 '19 at 05:47
your actual outcome does not correspond to the query. it will definitely return four rows for your data since you have four different clothes. — Radim Bača, Feb 06 '19 at 07:59
Sorry guys, that was a poor example, I'll make a better example but not sure if I should post it here or make a new post.... — BFF, Feb 07 '19 at 01:20

Salman A · Accepted Answer · 2019-02-06T08:39:12.850

You probably need to group twice (1) by item number (2) by item category:

SELECT SUBSTR(item_no, 5, 1) AS category
     , COUNT(*) AS count_products
     , SUM(count_small) AS small
     , SUM(count_med) AS med
     , SUM(count_large) AS large
     , SUM(CASE WHEN count_small + count_med + count_large > 1 THEN 1 END) AS has_multiple
FROM (
    SELECT b.ITEM_NO
         , COUNT(CASE WHEN c.SIZE = 'S' THEN 1 END) AS count_small
         , COUNT(CASE WHEN c.SIZE = 'M' THEN 1 END) AS count_med
         , COUNT(CASE WHEN c.SIZE = 'L' THEN 1 END) AS count_large
    FROM b
    LEFT JOIN c ON b.item_no = c.item_no
    GROUP BY b.ITEM_NO
) x
GROUP BY SUBSTR(item_no, 5, 1)

| category | count_products | small | med | large | has_multiple |
| C        | 4              | 1     | 3   | 1     | 1            |

And the variation:

SELECT SUBSTR(item_no, 5, 1) AS category
     , COUNT(*) AS count_products
     , SUM(CASE WHEN count_small + count_med + count_large = 1 THEN count_small END) AS small
     , SUM(CASE WHEN count_small + count_med + count_large = 1 THEN count_med   END) AS med
     , SUM(CASE WHEN count_small + count_med + count_large = 1 THEN count_large END) AS large
     , SUM(CASE WHEN count_small + count_med + count_large > 1 THEN 1 END) AS has_multiple
FROM (
    SELECT b.ITEM_NO
         , COUNT(CASE WHEN c.SIZE = 'S' THEN 1 END) AS count_small
         , COUNT(CASE WHEN c.SIZE = 'M' THEN 1 END) AS count_med
         , COUNT(CASE WHEN c.SIZE = 'L' THEN 1 END) AS count_large
    FROM b
    LEFT JOIN c ON b.item_no = c.item_no
    GROUP BY b.ITEM_NO
) x
GROUP BY SUBSTR(item_no, 5, 1)

| category | count_products | small | med | large | has_multiple |
| C        | 4              | 0     | 2   | 1     | 1            |

Thanks, I was able to follow your logic - but I still couldn't get the 'has_multiple' to work, it shows everything as "1"... I'll post a more realistic example. Not sure if I can just edit this post - or mark this as solved then post a new one... — BFF, Feb 07 '19 at 01:24
This matches your test cases. Consider asking a new question if the logic is different. — Salman A, Feb 07 '19 at 04:10

score 0 · Answer 2 · answered Feb 06 '19 at 07:11

--creando tabla
create table #temp (itemId int, size nvarchar(1))

--insertando valores
insert into #temp values (1,'S')
insert into #temp values (1,'M')
insert into #temp values (2,'M')
insert into #temp values (3,'L')
insert into #temp values (4,'M')


-- table of Different Item Codes

    select 
        itemId
    into #masDeUnItem
    from
    (select itemId,size from #temp group by itemId,size) t1
    group by itemId 
    having count(1) > 1


-- Variable of Counting different Items
declare @itemsDistintos int 


-- Providing Value to Variable
select @itemsDistintos = count(1) from 
(
    select * from #masDeUnItem
) t1


--Outcome 1
select count(distinct(itemId)) TOTAL
,
sum(case when size = 'S' then 1 else 0 end) SMALL
, sum(case when size = 'M' then 1 else 0 end) MEDIUM
, sum(case when size = 'L' then 1 else 0 end) LARGE
,  @itemsDistintos as Multiple_Sizes
from #temp


--Outcome 2
select count(distinct(a.itemId)) TOTAL
,
sum(case when size = 'S' and b.itemId is null then 1 else 0 end) SMALL
, sum(case when size = 'M' and b.itemId is null then 1 else 0 end) MEDIUM
, sum(case when size = 'L' and b.itemId is null then 1 else 0 end) LARGE
,  @itemsDistintos as Multiple_Sizes
from #temp a 
left join #masDeUnItem b 
on a.itemId = b.itemId

How to sum up unique values using case statements having certain conditions

2 Answers2