Calculating Values Created with a CASE

[VLG711]

HI,
I'm using a CASE statment to count values in a column. a sample is..

Rowdate Trunk DNIS
2007071604 6 30003
2007071604 5 30003
2007071604 4 30003

I have created the following query which works correctly..

SELECT RowDate, DNIS
SUM(CASE
WHEN trunk = 4 THEN 1
ELSE 0
END) trunk_4_Total,
SUM(CASE
WHEN trunk = 5 THEN 1
ELSE 0
END) trunk_5_Total,
SUM(CASE
WHEN trunk = 6 THEN 1
ELSE 0
END) trunk_6_Total,
count(*) as Total_Calls,

FROM TrunkHistory
where sourceid = 1
and RowDate between 2007071604 and 2007071605
and Trunk in (4, 5, 6)
and DNIS = 30003
group by DNIS, RowDate
order by DNIS, RowDate asc

However i now need to know what the percentage of each value is in relation to the total. IE..

((trunk_4_Total / (trunk_4_Total + trunk_5_Total + trunk_6_Total)) * 100)

But I'm not sure how to work the syntax into the query.

Any help is appreciated, Thanks in advance!

VLG711

 

[gmmastros]

The simple way is to use a derived table, like this...

Select RowDate, 
       DNIS,
       trunk_4_total,
       ((trunk_4_Total / (trunk_4_Total + trunk_5_Total + trunk_6_Total)) * 100) As trunk_4_percent,
       trunk_5_total
From   (
       [blue]SELECT RowDate, DNIS,
         SUM(CASE
               WHEN trunk = 4 THEN 1
               ELSE 0
             END) trunk_4_Total,
         SUM(CASE
               WHEN trunk = 5 THEN 1
               ELSE 0
             END) trunk_5_Total,
         SUM(CASE
               WHEN trunk = 6 THEN 1
               ELSE 0
             END) trunk_6_Total,
       count(*) as Total_Calls
       FROM TrunkHistory
       where sourceid = 1
       and RowDate between 2007071604 and 2007071605
       and Trunk in (4, 5, 6)
       and DNIS = 30003
       group by DNIS, RowDate
[/blue]       ) As A
order by DNIS, RowDate asc

Notice the part in blue is your original query, which becomes the derived table. Now you can use it to perform more calculations.

Make sense?

-George

"the screen with the little boxes in the window." - Moron

 

[bborissov]

Try:

[COLOR=blue]SELECT[/color] RowDate, DNIS
  SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] trunk = 4
           [COLOR=blue]THEN[/color] 1
           [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) trunk_4_Total,
  SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] trunk = 5
           [COLOR=blue]THEN[/color] 1
           [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) trunk_5_Total,
  SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] trunk = 6
           [COLOR=blue]THEN[/color] 1
           [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) trunk_6_Total,
  SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] trunk = 4
           [COLOR=blue]THEN[/color] 1
           [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) / [COLOR=#FF00FF]COUNT[/color](*) * 100.0 [COLOR=blue]AS[/color] trunk_4_Perc,
  SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] trunk = 5
           [COLOR=blue]THEN[/color] 1
           [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) / [COLOR=#FF00FF]COUNT[/color](*) * 100.0 [COLOR=blue]AS[/color] trunk_5_Perc,
  SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] trunk = 6
           [COLOR=blue]THEN[/color] 1
           [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) / [COLOR=#FF00FF]COUNT[/color](*) * 100.0 [COLOR=blue]AS[/color] trunk_6_Perc,
[COLOR=#FF00FF]count[/color](*) [COLOR=blue]as[/color] Total_Calls
[COLOR=blue]FROM[/color] TrunkHistory
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] 
     [COLOR=blue]where[/color] sourceid = 1
           and RowDate between 2007071604 and 2007071605
           and Trunk in (4, 5, 6)
           and DNIS = 30003
[COLOR=blue]group[/color] [COLOR=blue]by[/color] DNIS, RowDate
[COLOR=blue]order[/color] [COLOR=blue]by[/color] DNIS, RowDate [COLOR=#FF00FF]asc[/color]

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[VLG711]

Thanks Very Much! I will try them both.

 

[bborissov]

George's one is better.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP