Crosstab with multiple aggregates and columns 1

[elsenorjose]

This is a hard one to describe because you really have to see it but can a crosstab query be designed in such a way as to give me multiple aggregates under multiple column headers with multiple row headers? I have a table that contains data on online radio station activity by market, station, and also by the type of format 'plays' or 'streams' are delivered in:

tbl_Plays
market
station
bytes
sessions
duration
type

What I need to produce is a report has a layout something like this. (Please note that the metrics under each 'type' are sums of sessions, bytes, and duration).

Type1 Type2
market1 station 1 Plays MBytes TtlTime Plays MBytes TtlTime
market2 station 2 Plays MBytes TtlTime Plays MBytes TtlTime
...

First off, I don't even know if something like this is possible. Secondly, if it is, how would I write it using SQL? I'm not using PHP or any other front end. If anyone thinks they can help but would rather see the actual mock up of the report in Excel, I'd be glad to provide the spreadsheet via email. Thanks.

 

[r937]

very possible

clumsy, awkward, but possible

how many "types" are there? what are the datatypes of the bytes, sessions, duration columns?


r937.com | rudy.ca

 

[elsenorjose]

Thanks for replying...

The number of types isn't set in stone. A new program type can be added at any time and the server logs reflect this.

Bytes, sessions, and duration are all SMALLINT(5). Market, Type, and Station are VARCHAR(20)

For now I'm hacking it in Excel and just creating a pivot there. But it too has its limitations, not the least of which is 20+MB files once the data set gets big.

 

[r937]

select market
     , station
     , sum(case when type=1 then bytes    end) as type_1_bytes
     , sum(case when type=1 then sessions end) as type_1_bytes
     , sum(case when type=1 then duration end) as type_1_bytes
     , sum(case when type=2 then bytes    end) as type_2_bytes
     , sum(case when type=2 then sessions end) as type_2_bytes
     , sum(case when type=2 then duration end) as type_2_bytes
  from tbl_Plays
group
    by market
     , station

just extend the pattern as new types are defined




r937.com | rudy.ca

 

[elsenorjose]

Sweet.

Extending this a bit further...

If I had TWO tables with identical structures and wanted to combine the results into one query set BUT there may not be common markets, how would I write that?

But for now this is perfect. I can write this twice if needed and just combine the data in Excel.

Thanks Rudy.

 

[r937]

how would i handle your two tables?

simply replace this --

  from tbl_Plays

with this --

  from (
       select market
            , station
            , bytes    
            , sessions 
            , duration 
         from tbl_Plays
       union all
       select market
            , station
            , bytes    
            , sessions 
            , duration 
         from other_table
       ) as data

r937.com | rudy.ca

 

[elsenorjose]

You rock dude