February 25, 2010 at 2:27 am
Hi All,
I have the below query which retuns one row of data, how can I get the data grouped on the column aliases so i can have five rows for each column with the count side by side.
SELECT COUNT(CASE WHEN DATEDIFF(dd, createdon, modifiedon) < 1 THEN 1
END) AS '< 1' ,
COUNT(CASE WHEN DATEDIFF(dd, createdon, modifiedon) > 1
AND DATEDIFF(dd, createdon, modifiedon) <= 3 THEN 1
END) AS '>1 and <= 3' ,
COUNT(CASE WHEN DATEDIFF(dd, createdon, modifiedon) > 3
AND DATEDIFF(dd, createdon, modifiedon) <= 5 THEN 1
END) AS '>3 and <= 5' ,
COUNT(CASE WHEN DATEDIFF(dd, createdon, modifiedon) > 5
AND DATEDIFF(dd, createdon, modifiedon) <= 10 THEN 1
END) AS '>5 and <=10' ,
COUNT(CASE WHEN DATEDIFF(dd, createdon, modifiedon) > 10 THEN 1
END) AS '>10'
FROM Table
Thanks in advance
February 25, 2010 at 3:55 am
Try this
SELECT range , Count FROM
(
SELECT COUNT(CASE WHEN DATEDIFF(dd, createdon, modifiedon) < 1 THEN 1
END) AS '< 1' ,
COUNT(CASE WHEN DATEDIFF(dd, createdon, modifiedon) > 1
AND DATEDIFF(dd, createdon, modifiedon) <= 3 THEN 1
END) AS '>1 and <= 3' ,
COUNT(CASE WHEN DATEDIFF(dd, createdon, modifiedon) > 3
AND DATEDIFF(dd, createdon, modifiedon) <= 5 THEN 1
END) AS '>3 and <= 5' ,
COUNT(CASE WHEN DATEDIFF(dd, createdon, modifiedon) > 5
AND DATEDIFF(dd, createdon, modifiedon) <= 10 THEN 1
END) AS '>5 and <=10' ,
COUNT(CASE WHEN DATEDIFF(dd, createdon, modifiedon) > 10 THEN 1
END) AS '>10'
FROM Table
) P
UNPIVOT
(
Count for range IN
(
[< 1], [>1 and <= 3], [>3 and <= 5], [>5 and <=10], [>10]
)
) AS UP
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
February 25, 2010 at 3:56 am
Convert the existing query to derived table and apply UNPIVOT operator on the columns.
--Ramesh
February 25, 2010 at 4:43 am
Thanks this did the trick.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply