CODEWITHSUNDEEP
sqlsql-server
Mahee
Mahee

Reputation: 63

Finding unique rows

I am trying to pull items from a certain table that match between month 11 2018 ans month 11 2019, to get the number of unique items retained. The table looks something like the below. How do I obtain this result?

ItemId, Store#, Year, Month
1234, 21, 2018, 11
2345, 22, 2019, 11
1234, 21, 2019, 11

Thanks in advance !

Upvotes: 0

Views: 63

Answers (2)

Pan
Pan

Reputation: 341

Gordons answer will filter rows correctly. Adding COUNT(DISTINCT()) will give you the number of unique items.

SELECT COUNT(DISTINCT(t.ItemId)) AS NumUnique
FROM t
WHERE t.year * 12 + month >= 2018 * 12 + 11
AND t.year * 12 + month <= 2019 * 12 + 11
;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If I understand correctly, you just want to filter on year and month, but they are in different columns.

Here is one method:

select t.*
from t
where t.year * 12 + month >= 2018 * 12 + 11 and
      t.year * 12 + month <= 2019 * 12 + 11;

Upvotes: 1

Related Questions