count of distinct

Analytics
Enthusiast

count of distinct

How to get the count(combination of distinct values of two columns together).

i am giving the query in the following way

select count(distinct INVENTORY_ITEM_KEY,COST_TYPE_KEY) from ITEM_COST_DETAILS_F

but it is giving error at INVENTORY_ITEM_KEY and ','
3 REPLIES
Enthusiast

Re: count of distinct

You can concatenate the columns together:

select count(distinct INVENTORY_ITEM_KEY || COST_TYPE_KEY) from ITEM_COST_DETAILS_F

Be aware that this will convert both columns to a CHARACTER data type before concatenating them, so you'll want to make sure the format of the column will properly report all values.

Hope that helps.
Fan

Re: count of distinct

If the text conversion causes issues, you may want to try a GROUP BY of INVENTORY_ITEM_KEY,COST_TYPE_KEY instead of concatenating.

Re: count of distinct

have you try this :
select count(*) from
select INVENTORY_ITEM_KEY,COST_TYPE_KEY
from ITEM_COST_DETAILS_F
group by INVENTORY_ITEM_KEY,COST_TYPE_KEY ) as MYCOUNT