Sub Queries - 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

Sub Queries

Author

Topic

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-15 : 07:07:03

I have the following:

Task Complete_On_Time
Analysis Yes
Report No
Analysis No
Report Yes
Automation Yes
Report No

I want to return the following via an SQL query:

Task Count Complete_On_Time%
Analysis 2 0.5
Report 3 0.33
Automation 1 1

I have the following so far:

SELECT Task, Count(Task) as Count
FROM Table
GROUP BY Task

but I'm not sure how to achieve the third column. I think this requires a sub query and a calculated field?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-15 : 07:20:14

SELECT Task, Count(Task) as Count, 100.0/(COUNT(Task)*100) AS Complete_On_Time%
FROM Table
GROUP BY Task

--
Chandu

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-15 : 07:27:42

Sorry I think my example was misleading. I want that third column to sum the number of 'Yes's in the third column and divide by the Count of that Task, so for example Analysis occurs twice, but is only on time once, so that would be 1/2.
If analysis had occured 30 times and been On Time only 6 times, the calculated field would return 6/30.

Thanks.

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-15 : 07:28:56

When I said third column, I meant the second column.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-15 : 07:38:55

SELECT Task, Count(Task) as Count, CAST(SUM(CASE WHEN complete_on_Time = 'Yes' THEN 1 ELSE 0 END) AS DEC(5,2))/ COUNT(task) AS Complete_On_Time%
FROM @tasks
GROUP BY Task

--
Chandu

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-15 : 07:45:01

Returns the error 'Incorrect column expression:' and then lists the formula. I've entered it correctly, as far as I can see.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-15 : 07:54:32

May be this?

declare @tab table (col1 varchar(20),col2 varchar(4))
INSERT INTO @tab
VALUES('Analysis','Yes'),('Report','No'),('Analysis','No'),('Report','Yes'),('Automation','Yes'),('Report','No')
SELECT col1
,COUNT(1) Total
,Convert(int,Sum(Case When col2='yes' then 1 Else 0 END)) OnTimeYes
,Convert(int,Sum(Case When col2='no' then 1 Else 0 END)) OnTimeNo
,CONVERT(numeric(5,2),Convert(int,Sum(Case When col2='yes' then 1 Else 0 END))/convert(numeric(19,2),count(1)))
FROM @tab
group by col1

Cheers
MIK

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-15 : 07:57:03

quote:
Originally posted by stoolpidgeon

Returns the error 'Incorrect column expression:' and then lists the formula. I've entered it correctly, as far as I can see.

SELECT Task, Count(Task) as Count, CAST(SUM(CASE WHEN complete_on_Time = 'Yes' THEN 1 ELSE 0 END) AS DEC(5,2))/ COUNT(task) AS Complete_On_Time%
FROM @tasks -- Place your table name instead of @tasks
GROUP BY Task

--
Chandu

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-15 : 08:51:17

The code I used was:

SELECT Task_Main_Cat,
Count(Task_Main_Cat) as Task_Count,
CAST(SUM(CASE WHEN On_Time = 'OT' THEN 1 ELSE 0 END) AS DEC(5,2)) / COUNT(Task_Main_Cat) AS Complete_On_Time
FROM Completed$
GROUP BY Task_Main_Cat

where Task_Main_Cat and On_Time are the fields I'm using in table Completed$ ($ necessary apparently because I'm linking to another excel table)

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-15 : 08:53:58

I've also just tried the following, which didn't work either:

SELECT Task_Main_Cat,
Count(Task_Main_Cat) as Task_Count,
CONVERT(INT, SUM(CASE WHEN On_Time = 'OT' THEN 1 ELSE 0 END)) / COUNT(Task_Main_Cat) AS Complete_On_Time
FROM Completed$
GROUP BY Task_Main_Cat

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-15 : 08:55:14

Just to reiterate the error is specifically with the CAST formula.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-15 : 09:04:25

On which database you are working?
Post us the actual error message...

--
Chandu

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-15 : 09:11:39

I'm linking one excel workbook to another via Data>From Other Sources>From Microsoft Query.

The Error is:

Incorrect column expression: 'CAST(SUM(CASE WHEN On_Time = 'OT' THEN 1 ELSE 0 END) as DEC(5,2)) / COUNT(Task_Main_Cat)'.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-15 : 09:16:51

The above query will work on SQL Server only...

Which database( Oracle, Ms-Access or SQL Server) you are accessing from Microsoft Query?

--
Chandu

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-15 : 09:19:44

I'm linking between two excel workbooks, so I'm not querying a database as such, just a table on another spreadsheet.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-15 : 09:33:38

This link may help you
http://office.microsoft.com/en-in/excel-help/ways-to-sum-values-in-a-worksheet-HA010351133.aspx#BM2

This is the forum for SQL Server related tasks

--
Chandu

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-04-15 : 09:36:46

Thank you I'll take a look.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-15 : 09:42:13