Help With Query - 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

General SQL Server Forums

New to SQL Server Programming

Help With Query

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2009-10-07 : 09:49:12

I usually don't need to write sophisticated queries - mainly simple Select or Update or Delete queries. I'm struggling with the following.

I have a table named TEST and two columns in the table are of interest. TS_USER_05 (which I group by) and TS_EXEC_STATUS (which I report on) - hopefully this will make sense by illustrating the rows I need to see ... I need to return rows that looks like this example:

CON 3 3 100%
DPA 4 2 50%
MLM 4 1 25%
WBK 6 0 0%

Column 1 is from TS_USER_05 (the group by column)
Column 2 is the row count
Column 3 is the row count where TS_EXEC_STATUS <> 'No Run'
Column 4 is Column 3 divided by Column 2 (percentage)

I have written the following SQL which produces incorrect results for Columns 2 and 3 and I can't get Column 4 - percentage - to work at all:

SELECT TEST.TS_USER_05 as 'App.' ,
(SELECT Count(*) FROM TEST) AS 'Total Scripts',
(SELECT Count(*) FROM TEST WHERE TEST.TS_EXEC_STATUS <> 'No Run') AS 'Comp.'
FROM TEST GROUP BY TEST.TS_USER_05

The above query returns this:

CON 17 12
DPA 17 12
MLM 17 12
WBK 17 12

1. How do I correct my SQL to get correct results for columns 2 and 3?

2. How do I get a percentage to appear?

Thanks

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2009-10-07 : 09:51:36

Sorry, my bad query returns the following incorrect results:

CON 17 6
DPA 17 6
MLM 17 6
WBK 17 6

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-07 : 09:57:10

SELECT TEST.TS_USER_05 as [App.] ,
count(*) [Total Scripts],
sum(case when TEST.TS_EXEC_STATUS <> 'No Run' then 1 else 0 end) AS [Comp.],
sum(case when TEST.TS_EXEC_STATUS <> 'No Run' then 1 else 0 end) AS [Comp.]*100.0/count(*) as [precentage]
FROM TEST
GROUP BY TEST.TS_USER_05

Madhivanan

Failing to plan is Planning to fail

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2009-10-07 : 10:22:29

Thanks. This fixed Columns 2 and 3 but the percentage was giving an error: "near *" is what the message said. So I changed the query to the following and all works great!! Thanks again.

SELECT TEST.TS_USER_05 as [App.] ,
count(*) [Total Scripts],
sum(case when TEST.TS_EXEC_STATUS <> 'No Run' then 1 else 0 end) AS [Comp.],
sum(case when TEST.TS_EXEC_STATUS <> 'No Run' then 1 else 0 end)*100/count(*) as [%]
FROM TEST
GROUP BY TEST.TS_USER_05

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-07 : 10:31:00

quote:
Originally posted by planetoneautomation

Thanks. This fixed Columns 2 and 3 but the percentage was giving an error: "near *" is what the message said. So I changed the query to the following and all works great!! Thanks again.

SELECT TEST.TS_USER_05 as [App.] ,
count(*) [Total Scripts],
sum(case when TEST.TS_EXEC_STATUS <> 'No Run' then 1 else 0 end) AS [Comp.],
sum(case when TEST.TS_EXEC_STATUS <> 'No Run' then 1 else 0 end)*100/count(*) as [%]
FROM TEST
GROUP BY TEST.TS_USER_05

Yes. It was my cpoy/paste error. You dont need alias there

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-07 : 10:36:03

Note that you should use 100.0 instead of 100 for accuracy
See here
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources