February 18, 2008 at 2:24 pm
Hi All,
I have a question regarding recorcounts. I have a set of records that i got after writing the sql query. The record layout is something like:
ID Amountdue
1 5
2 10
3 40
4 0
5 -6
6 230
7 -5
8 100
9 -1
10 0
I want to get the count of each amount due more than 0, less than 0 and equal to 0 all in the same query. So the result should look something like
Order PassGT0 PassLT0 PassEQ0
1 5 NULL NULL
2 NULL 3 NULL
3 NULL NULL 2
i can do 3 separate select count(*) if these were stored in a table but the result set was created using joins in the Query Analyser. I can also achieve the above result using unions with 3 separate queries with different conditions but this way i have to join the tables 3 times in each query and this is slowing down the query.
Please let me know if there is some other way i can achieve this.
Thanks
February 18, 2008 at 2:38 pm
You could use a sum case for this something along the lines of:
SELECT ID,
SUM(CASE WHEN AMOUNTDUE > 0 THEN 1 ELSE 0 END) AS [GT 0],
SUM(CASE WHEN AMOUNTDUE < 0 THEN 1 ELSE 0 END) AS [LT 0],
SUM(CASE WHEN AMOUNTDUE = 0 THEN 1 ELSE 0 END) AS [EQ 0]
FROM MYTABLE
GROUP BY ID
Edited for sytanx error
February 18, 2008 at 3:01 pm
You can also do a count case.
SELECT ID,
COUNT(CASE WHEN AMOUNTDUE > 0 THEN ID END) AS [GT 0],
COUNT(CASE WHEN AMOUNTDUE < 0 THEN ID END) AS [LT 0],
COUNT(CASE WHEN AMOUNTDUE = 0 THEN ID END) AS [EQ 0]
FROM MYTABLE
GROUP BY ID
February 19, 2008 at 6:18 am
Hi abhi,
try the below one. it may help you to sort out the problem. 😀
DROP TABLE #temp
CREATE TABLE #temp (ID int,AmountDue int)
INSERT INTO #temp (ID,AmountDue)
SELECT 1,5
UNION ALL
SELECT 2,10
UNION ALL
SELECT 3,40
UNION ALL
SELECT 4,0
UNION ALL
SELECT 5,-6
UNION ALL
SELECT 6,230
UNION ALL
SELECT 7,-5
UNION ALL
SELECT 8,100
UNION ALL
SELECT 9,-1
UNION ALL
SELECT 10,0
SELECT * FROM #temp
SELECT COUNT(CASE WHEN GTO >= 1 THEN 1 ELSE NULL END) AS GTO
,COUNT(CASE WHEN LTO >= 1 THEN 1 ELSE NULL END) AS LTO
,COUNT(CASE WHEN ETO >= 1 THEN 1 ELSE NULL END) AS ETO
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS [Order],
COUNT(CASE WHEN AmountDue > 0 THEN 1 ELSE NULL END) AS GTO,
COUNT(CASE WHEN AmountDue < 0 THEN 1 ELSE NULL END) AS LTO,
COUNT(CASE WHEN AmountDue = 0 THEN 1 ELSE NULL END) AS ETO
FROM #temp
GROUP BY ID) AS P
---
February 19, 2008 at 7:53 am
Hi Adam,
I was looking at something similar to what you provided. it works great thanks.
Abhi
February 19, 2008 at 8:03 am
No problem. Thanks for the feedback.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply