Jump to content
  • 0
Sign in to follow this  
Elena

Conditional Count Of Columns Sql

Question

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,

    
    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.
I do not need to count the values themselves. 
 
Thank you. 
Edited by Elena

Share this post


Link to post
Share on other sites

7 answers to this question

Recommended Posts

  • 0

Hi Elena,

 

Create a calculated field and paste the following statement. Use picker for field names to avoid misspellings.

 

CASE
WHEN (([@field:PayscaleAverage])>0) AND (([@field:ERI_Median ])>0) AND (([@field:Miliman_Avg])>0) THEN 3
WHEN (([@field:PayscaleAverage])>0) AND ((([@field:ERI_Median ])>0) OR (([@field:Miliman_Avg])>0)) THEN 2    WHEN ((([@field:PayscaleAverage])>0) OR (([@field:ERI_Median ])>0)) AND (([@field:Miliman_Avg])>0) THEN 2
WHEN (([@field:PayscaleAverage])>0) OR (([@field:ERI_Median ])>0) OR (([@field:Miliman_Avg])>0) THEN 1
ELSE 0
END

Hope it helps.

Share this post


Link to post
Share on other sites
  • 0

Hi Matilda, 

 

Thank you for the guidance. Is there a way to shorten this conditional query? 

I have 25 columns that I am trying to count. So with the code above, I would have to do 25 fields for 25 queries.

 

As experience told me, with Caspio the greater the queries, the slower the page to load when codes are embedded with html page. 

 

Can you recommend a SELECT query? 

 

Thank you again. 

E

Share this post


Link to post
Share on other sites
  • 0

Hi,

 

I have used an aggregation field and a formula like:

(CASE WHEN SUM(one)>0 THEN 1 ELSE 0 END)+(CASE WHEN SUM(two)>0 THEN 1 ELSE 0 END)+(CASE WHEN SUM(three)>0 THEN 1 ELSE 0 END)+(CASE WHEN SUM(four)>0 THEN 1 ELSE 0 END)+(CASE WHEN SUM(five)>0 THEN 1 ELSE 0 END)

I know, it's not a brilliant solution, but it works.

Share this post


Link to post
Share on other sites
  • 0

Hello Walter,

 

Thank you for the advice. Your code works, but I must be doing something wrong because it is counting columns with zero values as 1 as well. Effectively ignoring the ELSE. 

 

Here is the code based on your recommendation (I customized for my purpose).

SELECT

(CASE WHEN SUM(PayscaleAverage) >0 THEN 1 ELSE 0 END)+
(CASE WHEN SUM (ERI_Median) >0 THEN 1 ELSE 0 END)+
(CASE WHEN SUM(Miliman_Avg) >0 THEN 1 ELSE 0 END)+
(CASE WHEN SUM(TheLeagueCounties_Avg) >0 THEN 1 ELSE 0 END)+
(CASE WHEN SUM(TheLeagueCities_Avg) >0 THEN 1 ELSE 0 END)
 
From Market_Data

The problem is,  I am getting a total number of 5, when actually the column "ERI_Median" has a value of 0.

I should be getting 4. But it's not the case.

 

 

 

On a slightly different note...

 

any idea why "GROUP BY" doesn't seem to be acceptable in Caspio?  I can't get GROUP BY to work.

Share this post


Link to post
Share on other sites
  • 0

Hi Elena, 

 

Try the following solution:

CASE WHEN ([@field:PayscaleAverage]) > 0 THEN 1 else 0 END +
CASE WHEN ([@field:ERI_Median]) > 0 THEN 1 else 0 END + CASE WHEN ([@field:Miliman_Avg]) > 0 THEN 1 else 0 END

Let me know if it helps.

Share this post


Link to post
Share on other sites
  • 0
I want to use the countif function for 7 columns in a table. i want the find howmany values are not null in 7 columns and get the result

Eg. col1,col2,col3,col4,col5,col6,col7

1 0 0 0 0 1 0

i want to write the query for getting the count value in the above table. The output for the above is 2. Please advise how to write the query for the same.

Share this post


Link to post
Share on other sites
  • 0

Hi Gradient,

 

Create a calculated field and use the following query: 

CASE WHEN ([field1]) > 0 THEN 1 else 0 END +
CASE WHEN ([field2]) > 0 THEN 1 else 0 END + CASE WHEN ([field3]) > 0 THEN 1 else 0 END + CASE WHEN ([field4]) > 0 THEN 1 else 0 END + CASE WHEN ([field5]) > 0 THEN 1 else 0 END + CASE WHEN ([field6]) > 0 THEN 1 else 0 END + CASE WHEN ([field7]) > 0 THEN 1 else 0 END

Use picker to paste field names instead of field1...field7. 

 

Hope it helps.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×