Jump to content
  • 0

Conditional Count Of Columns Sql


Elena

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
Link to comment
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.

Link to comment
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

Link to comment
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.

Link to comment
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.

Link to comment
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.

Link to comment
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.

Link to comment
Share on other sites

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.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...