I am trying to count the number of columns where values are more than zero. I tried both "SUM" and "COUNT", in a calculated field, but my query is not working. I also want to sum the total count.
I get this error"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Can you please take a look and please let me know my mistake?
SELECT ID,
SUM(CASE WHEN PayscaleAverage >0 then 1 else 0 end) as one,
SUM(CASE WHEN ERI_Median >0 then 1 else 0 end) as two,
SUM(CASE WHEN Miliman_Avg >0 then 1 else 0 end) as three
FROM market_data
GROUP by ID
I also tried "COUNT" but it's not working either.
SELECT ID,
COUNT(CASE WHEN PayscaleAverage >0 then 1 end) as one,
COUNT(CASE WHEN ERI_Median >0 then 1 end) as two,
COUNT(CASE WHEN Miliman_Avg >0 then 1 end) as three
FROM market_data
GROUP by ID
Thank you,
Elena
======
Edit:
I would like to clarify that I only want to count the number of columns that has values greater than zero.
You can post now and register later.
If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.
Question
Elena
Hello there,
I am trying to count the number of columns where values are more than zero. I tried both "SUM" and "COUNT", in a calculated field, but my query is not working. I also want to sum the total count.
I get this error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Can you please take a look and please let me know my mistake?
SELECT ID,
Link to comment
Share on other sites
7 answers to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.