Query with subqueries

Hi
Is it possible to group in a subquery.
declare @.ext char(4)
declare @.site int
declare @.calltype char(1)
set @.site = 1
set @.calltype = 'E'
select digits, count(*) as total,
(select count(*) from V2Tickets where durationofconversation>0 and
siteid=@.site and calltype=@.calltype and len(digits)=4) as besvaret,
(select count(*) from V2Tickets where durationofconversation=0 and
releasecause='OC' and siteid=@.site and calltype=@.calltype and len(digits)=4)
as optaget,
(select count(*) from V2Tickets where durationofconversation=0 and
durationofcall<40 and releasecause='RL' and siteid=@.site and
calltype=@.calltype and len(digits)=4) as opgivet,
(select count(*) from V2Tickets where durationofconversation=0 and
durationofcall>=40 and siteid=@.site and calltype=@.calltype and
len(digits)=4) as ubesvaret
from [V2tickets]
where
siteid=@.site and calltype=@.calltype and len(digits)=4
group by digits
order by 1
As you can see this gives me the correct total (colum 2) for digits, but all
the subqueries (column 3-6) computes the total.
1000 ,19167,370251,109377,58621,52809
1001 ,74,370251,109377,58621,52809
1002 ,139,370251,109377,58621,52809
1003 ,113,370251,109377,58621,52809
1004 ,81,370251,109377,58621,52809
1005 ,385,370251,109377,58621,52809
1006 ,307,370251,109377,58621,52809
1007 ,237,370251,109377,58621,52809
1008 ,122,370251,109377,58621,52809
1009 ,79,370251,109377,58621,52809
1010 ,255,370251,109377,58621,52809
1011 ,288,370251,109377,58621,52809
regards
HenryOn Mon, 14 Feb 2005 22:02:10 +0100, Henry wrote:

>Is it possible to group in a subquery.
>declare @.ext char(4)
>declare @.site int
>declare @.calltype char(1)
>set @.site = 1
>set @.calltype = 'E'
>select digits, count(*) as total,
>(select count(*) from V2Tickets where durationofconversation>0 and
>siteid=@.site and calltype=@.calltype and len(digits)=4) as besvaret,
>(select count(*) from V2Tickets where durationofconversation=0 and
>releasecause='OC' and siteid=@.site and calltype=@.calltype and len(digits)=4
)
>as optaget,
>(select count(*) from V2Tickets where durationofconversation=0 and
>durationofcall<40 and releasecause='RL' and siteid=@.site and
>calltype=@.calltype and len(digits)=4) as opgivet,
>(select count(*) from V2Tickets where durationofconversation=0 and
>durationofcall>=40 and siteid=@.site and calltype=@.calltype and
>len(digits)=4) as ubesvaret
>from [V2tickets]
>where
>siteid=@.site and calltype=@.calltype and len(digits)=4
>group by digits
>order by 1
>As you can see this gives me the correct total (colum 2) for digits, but al
l
>the subqueries (column 3-6) computes the total.
(snip)
Hi Henry,
You can fix this by expanding the subqueries with a reference to the row
in the outer query:
SELECT digits
, COUNT(*) AS total
,(SELECT COUNT(*)
FROM V2Tickets AS b
WHERE b.durationofconversation > 0
AND b.siteid = @.site
AND b.calltype = @.calltype
AND LEN(b.digits) = 4
AND b.digits = a.digits) AS besvaret
(other subqueries snipped)
FROM V2tickets AS a
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
GROUP BY digits
ORDER BY digits
However, in your case there's a faster method to get the various counts:
SELECT digits
, COUNT(*) AS total
, COUNT(CASE WHEN durationofconversation > 0
THEN 1 END) AS besvaret
, COUNT(CASE WHEN durationofconversation = 0
AND releasecouse = 'OC'
THEN 1 END) AS optaget
, COUNT(CASE WHEN durationofconversation = 0
AND releasecouse = 'RL'
AND durationofcall < 40
THEN 1 END) AS opgivet
, COUNT(CASE WHEN durationofconversation = 0
AND durationofcall >= 40
THEN 1 END) AS ubesvaret
FROM V2tickets
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
GROUP BY digits
ORDER BY digits
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> However, in your case there's a faster method to get the various counts:
> SELECT digits
> , COUNT(*) AS total
> , COUNT(CASE WHEN durationofconversation > 0
> THEN 1 END) AS besvaret
> , COUNT(CASE WHEN durationofconversation = 0
> AND releasecouse = 'OC'
> THEN 1 END) AS optaget
> , COUNT(CASE WHEN durationofconversation = 0
> AND releasecouse = 'RL'
> AND durationofcall < 40
> THEN 1 END) AS opgivet
> , COUNT(CASE WHEN durationofconversation = 0
> AND durationofcall >= 40
> THEN 1 END) AS ubesvaret
> FROM V2tickets
> WHERE siteid = @.site
> AND calltype = @.calltype
> AND LEN(digits) = 4
> GROUP BY digits
> ORDER BY digits
>
Hi Hugo
Hey, that was an excellent suggestion, that simplifies this quite a lot, and
it even works ;o)
I was think about, if it is possible to do calculations on the result
"fields" e.g
unidentifiedcalls = total - (besvaret+optaget+opgivet+ubesvaret)
or
percent besvaret = (total-unidentifiedcalls)/100 * besvaret
This is like a hole new world, I used to do this "manually" in my
application.
regards
Henry|||On Tue, 15 Feb 2005 19:06:48 +0100, Henry wrote:

>Hey, that was an excellent suggestion, that simplifies this quite a lot, an
d
>it even works ;o)
>I was think about, if it is possible to do calculations on the result
>"fields" e.g
>unidentifiedcalls = total - (besvaret+optaget+opgivet+ubesvaret)
>or
>percent besvaret = (total-unidentifiedcalls)/100 * besvaret
Hi Henry,
That is possible, but not directly. Since the column aliases are applied
after the select clause is evaluated (conceptually at least - optimizers
may and will change order of evaluation, but without changing the result),
you can't use these aliases is any part of the SELECT.
The workaround is to use a derived table.
So this query would fail:
SELECT Column1 + Column2 AS Sum1
Column1 - Column2 AS Diff1,
Sum1 * Diff1 AS Prod1
FROM SomeTable
But this query would work
SELECT Sum1, Diff1,
Sum1 * Diff1 AS Prod1
FROM (SELECT Column1 + Column2 AS Sum1
Column1 - Column2 AS Diff1
FROM SomeTable) AS D
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)