October 26, 2011 at 3:04 am
Hi,
I need to count Multiple columns from one table and display the count in one column
I'm triying to use a Case Statement to return the count but no luck so far, please help
I basically need to display total approvers of each requisition it can be 5 or less
Here's the query I'm struggling with:
Select intRequisitionId,
SUM(Case When Convert(varchar, dteFirstApproverDateNotified, 103) Is Not Null
OR Convert(varchar, dteSecondApproverDateNotified, 103) Is Not Null
OR Convert(varchar, dteThirdApproverDateNotified, 103) Is Not Null
OR Convert(varchar, dteFourthApproverDateNotified, 103) Is Not Null
OR Convert(varchar, dteFifthApproverDateNotified, 103) Is Not Null
Then ????? Else 0 End) as TotalApprovers
From dtlRequisition
Group By intRequisitionId
Thanks in advance
October 26, 2011 at 3:22 am
If replacing the ???? with 1 doesn't work then I think you need to explain this better. Also do you need the CONVERTs?
Regards
Daniel
October 26, 2011 at 3:26 am
I think from your query i understood that you need the count of each column. See below query that gives total count. I can't run on my DB since i dont have the data but it should work.
Select intRequisitionId,
SUM(CASE WHEN ISNULL(dteFirstApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column1,
SUM(CASE WHEN ISNULL(dteSecondApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column2,
SUM(CASE WHEN ISNULL(dteThirdApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column3,
SUM(CASE WHEN ISNULL(dteFourthApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column4,
SUM(CASE WHEN ISNULL(dteFifthApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column5
From dtlRequisition
Group By intRequisitionId
October 26, 2011 at 3:28 am
I had 1 where i have the question marks, but i want to display the actual count not the no.1 where the 'notified date' is not null
for all the approvers.
The converts don't really matter its just for grouping purposes
Or is there another way of achieving this?
October 26, 2011 at 3:35 am
Hi, This works perfectly, but its not How I need to display the data:
Select intRequisitionId,
SUM(CASE WHEN ISNULL(dteFirstApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column1,
SUM(CASE WHEN ISNULL(dteSecondApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column2,
SUM(CASE WHEN ISNULL(dteThirdApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column3,
SUM(CASE WHEN ISNULL(dteFourthApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column4,
SUM(CASE WHEN ISNULL(dteFifthApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column5
From dtlRequisition
Group By intRequisitionId
--This is the resultSet from the above query
intRequisitionIdcolumn1column2column3column4column5
26211100
--What I need is one Column with the Total Count
e.g.
intRequisitionIdTotal
2623
Thanks
October 26, 2011 at 3:38 am
Teee (10/26/2011)
Hi, This works perfectly, but its not How I need to display the data:Select intRequisitionId,
SUM(CASE WHEN ISNULL(dteFirstApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column1,
SUM(CASE WHEN ISNULL(dteSecondApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column2,
SUM(CASE WHEN ISNULL(dteThirdApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column3,
SUM(CASE WHEN ISNULL(dteFourthApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column4,
SUM(CASE WHEN ISNULL(dteFifthApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column5
From dtlRequisition
Group By intRequisitionId
--This is the resultSet from the above query
intRequisitionIdcolumn1column2column3column4column5
26211100
--What I need is one Column with the Total Count
e.g.
intRequisitionIdTotal
2623
Thanks
SELECT intRequisitionId, column1+column2+column3+column4+column5 AS total
FROM (SELECT intRequisitionId,
SUM(CASE WHEN ISNULL(dteFirstApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column1,
SUM(CASE WHEN ISNULL(dteSecondApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column2,
SUM(CASE WHEN ISNULL(dteThirdApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column3,
SUM(CASE WHEN ISNULL(dteFourthApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column4,
SUM(CASE WHEN ISNULL(dteFifthApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS column5
FROM dtlRequisition
GROUP BY intRequisitionId) a
October 26, 2011 at 3:41 am
I think Cadavre has given the right tip for you.
That should work.
🙂
October 26, 2011 at 3:43 am
This should be about right but I can't run it assuming that if ANY of the columns are null the the value of the row is 0.
---------------------
Select
intRequisitionId,
Sum(
Case
When dteFirstApproverDateNotified Is Null Then 0
Else 1
End
&
Case
When dteSecondApproverDateNotified Is Null Then 0
Else 1
End
&
Case
When dteThirdApproverDateNotified Is Null Then 0
Else 1
End
&
Case
When dteFourthApproverDateNotified Is Null Then 0
Else 1
End
&
Case
When dteFifthApproverDateNotified Is Null Then 0
Else 1
End
) As TotalApprovers
From
dtlRequisition
Group By
intRequisitionId
-------------------------------------
October 26, 2011 at 3:44 am
It works, Thank you very much!!! 🙂
October 26, 2011 at 3:49 am
Daniel your statement doesn't work because in case statement it only takes 1 value as output eventhough it matches the rest.
October 26, 2011 at 3:54 am
You can use below to avoid inner query.
Select intRequisitionId,
SUM(CASE WHEN ISNULL(dteFirstApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) +
SUM(CASE WHEN ISNULL(dteSecondApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) +
SUM(CASE WHEN ISNULL(dteThirdApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) +
SUM(CASE WHEN ISNULL(dteFourthApproverDateNotified,0) <> 0 THEN 1 ELSE 0 END ) AS Total
From dtlRequisition
Group By intRequisitionId
October 26, 2011 at 4:03 am
The SQL works fine. There is only one output from each of the CASE statements which are combined into one result with the bitwise AND (&) operator. The result will be 1 only if ALL the CASE statements return 1. Summing up the result will give you a count of all the records which don't have a null value in any of the columns. Whether this is what was required in the origional post is another matter.
October 26, 2011 at 4:07 am
Cadavre's query worked perfectly.
Thanks 🙂
October 26, 2011 at 4:09 am
Daniel Forrester 123 I ran your query as well, but it returned 0's for all rows.
October 26, 2011 at 4:20 am
Hi Teee, you can either use my Query from prev post to avoid inner statement OR
You can use Daniel's Query replace & with + that works perfectly.
Sorry Daniel - your Query is also right but it does give same output as my Query. I am using ISNULL that is more standard way of writing isn't it.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply