Determining the count of columns with a value greater than 0

Question

Ok guys/gals...

Here's my result set:

and here's the ultimate results set I want

to achieve the result, I am currently using n (3) subqueries... which is not ideal...

declare @userId int = 436
select
    (select count(PRODUCT_ID) from sl_suggested_asset where PRODUCT_ID > 0 and user_id = @userId) As 'PRODUCT_ID',
    (select count(DIGI_DOC_ID) from sl_suggested_asset where DIGI_DOC_ID > 0 and user_id = @userId) As 'DIGI_DOC_ID',
    (select count(QR_CODE_ID) from sl_suggested_asset where qr_code_id > 0  and user_id = @userId) As 'QR_CODE_ID'

what I would like is to use either a cte or an aggregate...

here's what I was thinking...

select count(product_id), count(DIGI_DOC_ID), count(qr_code_id), user_id
from sl_suggested_asset
where user_id = 436
group by user_id

but this gives me the total number of rows...

so my having clause should be used but that means that I need to have the proper group by predicate... this is where I'm stuck...

here are some examples of what I've tried...

select 
    count(PRODUCT_ID) As 'PRODUCT_ID'
    , count(DIGI_DOC_ID) As 'DIGI_DOC_ID'
    , count(QR_CODE_ID) As 'QR_CODE_ID'
    , user_id
from sl_suggested_asset
where 
    PRODUCT_ID > 0
or
    DIGI_DOC_ID > 0
or
    QR_CODE_ID > 0
and
    user_id = 436
group by user_id

and I get...

... here I change the order of the where clause conditions...

select 
    count(PRODUCT_ID) As 'PRODUCT_ID'
    , count(DIGI_DOC_ID) As 'DIGI_DOC_ID'
    , count(QR_CODE_ID) As 'QR_CODE_ID'
    , user_id
from sl_suggested_asset
where 
    user_id = 436
and
    PRODUCT_ID > 0
or
    DIGI_DOC_ID > 0
or
    QR_CODE_ID > 0  
group by user_id

... and I get the same result as above ...

... now I change the where clause to = 0 and get the same result, which means the where clause is being completely ignored...

select 
    count(PRODUCT_ID) As 'PRODUCT_ID'
    , count(DIGI_DOC_ID) As 'DIGI_DOC_ID'
    , count(QR_CODE_ID) As 'QR_CODE_ID'
    , user_id
from sl_suggested_asset
where 
    user_id = 436
and
    PRODUCT_ID = 0
or
    DIGI_DOC_ID = 0
or
    QR_CODE_ID = 0  
group by user_id

... lastly, if I change the ors to ands I get zero results ...

select 
    count(PRODUCT_ID) As 'PRODUCT_ID'
    , count(DIGI_DOC_ID) As 'DIGI_DOC_ID'
    , count(QR_CODE_ID) As 'QR_CODE_ID'
    , user_id
from sl_suggested_asset
where 
    user_id = 436
and
    PRODUCT_ID = 0
and
    DIGI_DOC_ID = 0
and
    QR_CODE_ID = 0  
group by user_id

Answer 1

Just use some CASE STATEMENTS on the Query

SELECT SUM( CASE WHEN PRODUCT_ID > 0 THEN 1 ELSE 0 END ) as PRODUCT_ID , SUM( CASE WHEN DIGI_DOC_ID>0 THEN 1 ELSE 0 END ) as DIGI_DOC_ID, SUM( CASE WHEN QR_CODE_ID>0 THEN 1 ELSE 0 END ) as QR_CODE_ID FROM sl_SUGGESTED_ASSET WHERE USER_ID =436

Have a great day!