How do I - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How do I

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-01-10 : 04:36:34

Hi Guys,
I have a table with a column showing the percentage a task is complete. These range from 0 - 100 in steps of 10.
What I need to do is, in 1 query, return the counts of all tasks that have 0, all tasks that are 10 - 90 and a all tasks that are 100.

Could you help me with how I would do that. I have tried but I can only do it 1 at a time. I need to do all 3 in the same query.

I appreciate your help.

Best Regards,

Steve

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 04:58:50

[code]SELECT SUM(CASE WHEN percentagecol=0 THEN 1 ELSE 0 END) AS [0 Count],
SUM(CASE WHEN percentagecol>=10 AND percentagecol<=90 THEN 1 ELSE 0 END) AS [10-90 Count],
SUM(CASE WHEN percentagecol=100 THEN 1 ELSE 0 END) AS [100 Count]
FROM YourTable[/code]

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-01-13 : 03:06:51

Thanks for the reply.

I will try that.

Best Regards,

Steve.

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 03:13:53

welcome

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-02-17 : 12:12:24

Hi Guys,

The post from visakh16 worked perfect but I now need to use Order By to show the result in numerical order so if visakh16 sql code returned 12, 2, & 8 I need it to be ordered 12, 8, & 2. How would I set an Order By on visakh16's code below.

SELECT SUM(CASE WHEN percentagecol=0 THEN 1 ELSE 0 END) AS [0 Count],
SUM(CASE WHEN percentagecol>=10 AND percentagecol<=90 THEN 1 ELSE 0 END) AS [10-90 Count],
SUM(CASE WHEN percentagecol=100 THEN 1 ELSE 0 END) AS [100 Count]
FROM YourTable

Thanks for your help.

Best Regards,

Steve.

Steve

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-17 : 12:36:07

Those are counts for separate columns.How do you want to order? Can you elaborate?

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-02-17 : 16:23:21

hi Sodeep,
Thanks for the reply.

The sql code returns:
0 Count 12
10-90 Count 4
100 Count 8

I would like it ordered Decending because I am using it in a Barchart. I would like it as:
0 Count 12
100 Count 8
10-90 Count 4

Thanks for your help.

Best Regards,

Steve.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 14:32:13

If I understand this correctly,
right now your result shows 12,2 and 8 and you want it to show 12,8,2.

And sometime later all processes are complete or something like that, you want it to be in descending order like 22,0,0.

Is that it? If yes, as far as I know, it cannot be done.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 14:33:04

You can maybe create a temp table and put all these values in a single column and do a order by.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 15:12:45

[code]select * from
(
SELECT SUM(CASE WHEN percentagecol=0 THEN 1 ELSE 0 END) ,'[0 Count]' as range
FROM YourTable
union all
SELECT
SUM(CASE WHEN percentagecol>=10 AND percentagecol<=90 THEN 1 ELSE 0 END) , '[10-90 Count]'
FROM YourTable
union all
SELECT
SUM(CASE WHEN percentagecol=100 THEN 1 ELSE 0 END) , '[100 Count]'
FROM YourTable
)s order by 1 desc[/code]

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-02-20 : 04:32:26

Hi sakets_2000,
Thanks for the reply.
Unfortunately your solution does not work.
At the end of your statement "FROM YourTable )s order by 1 desc" is the "s" after the ")" supposed to be there because I get an error of "no column was specified for column 1 of 's'
If I remove the "s" I get an error of
"Incorrect syntax near the keyword ORDER"

Thanks for the help.

Best Regards,

Steve.

Steve

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 05:16:56

Use this then,

select * from 
(
SELECT SUM(CASE WHEN percentagecol=0 THEN 1 ELSE 0 END) as value ,'[0 Count]' as range 
FROM YourTable
union all
SELECT  
SUM(CASE WHEN percentagecol>=10 AND percentagecol<=90 THEN 1 ELSE 0 END) , '[10-90 Count]'
FROM YourTable
union all
SELECT 
SUM(CASE WHEN percentagecol=100 THEN 1 ELSE 0 END) , '[100 Count]'
FROM YourTable
)s order by 1 desc

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-02-20 : 05:29:15

Hi sakets_2000,
Thanks for the quick reply.

That works perfect.
Thank you very much for taking time & sharing your knowledge.

Best Regards,

Steve.

Steve

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 06:11:48

np.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources