Matt Matt - 5 months ago 9
SQL Question

SQL - Count of Values Based on Multiple Keys

I am new to SQL and trying to create a query to return a count of unique values, but whether a row is counted depends on the value of another row that is related. I am unsure if this can be done by 'Group By' or some other function.

Let me try to explain. Here is what the data looks like. There is an indeterminate number of lines. For simplicity, I'm illustrating only two allowed values for each ITEM_NUMBER.

CUSTOMER_ID ITEM_NUMBER LINE DISPLAY_VALUE
1 10 1 A
1 20 1 Y
1 30 1 C
1 10 2 B
1 20 2 X
1 30 2 C
2 10 1 B
2 20 1 X
2 30 1 D
2 10 2 A
2 20 2 Y
2 30 2 D


Now, I only want to return valid lines for a CUSTOMER_ID when the value for ITEM_NUMBER 20 on the corresponding LINE/CUSTOMER_ID is equal to 'X'. These would be the first set of filtered results.

CUSTOMER_ID ITEM_NUMBER LINE DISPLAY_VALUE
1 10 2 B
1 20 2 X
1 30 2 C
2 10 1 B
2 20 1 X
2 30 1 D


Next, I want to get a count grouped by DISPLAY_VALUE and ITEM_NUMBER.
Sample Code:

SELECT COUNT(*) as 'Count', DISPLAY_VALUE, ITEM_NUMBER
FROM tmp.DB
GROUP BY DISPLAY VALUE, ITEM_NUMBER


Ideally, it would return the following:

Count ITEM_NUMBER DISPLAY_VALUE
2 10 B
2 20 X
1 30 C
1 30 D

Answer

I'm not able to test this but I think you need the following:

select count(*),DISPLAY_VALUE,ITEM_NUMBER
from tmp.DB
inner join (
   select LINE,CUSTOMER_ID 
   from tmp.DB
   where DISPLAY_VALUE = 'X'
   ) x on x.LINE=tmp.DB.LINE and x.CUSTOMER_ID=tmp.DB.CUSTOMER_ID
group by DISPLAY_VALUE,ITEM_NUMBER