Grouping (ish) assistance

[elbowe]

Hi All,
I know I have not posted before but I do find this site very good for SQL information, however, I am struggling with generating my required output from a table. Let me explain.

I have a table defined as (this is simplified) and with data in as below

Interval_timestamp bp-id countX

Date1+time1 0 5
Date1+time2 0 10
Date1+time1 1 2
Date1+time2 1 4
Date2+time1 0 20
Date2+time2 0 30
Date2+time1 1 10
Date2+time2 1 20

I would like to produce a report like (grouped on date and bp_id in date) with sum of the countX colume, but across the page rather than down :

Date bp_id sum(countX) bp_id sum(countX)

Date1 0 15 1 6
date2 0 50 1 30

i.e I want to have bp_id unique across the top followed by its sum of countX (this could be average or whatever on the total etc). I do not want the list in a sequential order (I can get that), but I want it in parallel.

There are many bp_id's but I have kept it simple with just two unique types. I need something dynamic which would collect all the bp_id's and then do as required.

Now not being an SQL whizz .. I wonder if you guys could help me?

many thanks

Leslie Pendlebury-Bowe
elbowe@yahoo.com

 

[r937]

what you describe is usually called a crosstab report

microsoft access has this functionality, but that's because microsoft access is more than a database engine, it's also a front-end application

with an unknown number of bp_id values, you cannot do this with SQL easily

my suggestion is to handle the formatting of the data in your front-end application or programming language

r937.com | rudy.ca

 

[elbowe]

This is on DB2 z/os, and yes I can achieve this with QMF, but I would rather not ... I am pleased that you have confirmed my thoughts that it is not possible in raw SQL.

If anybody else has some thoughts on this then please do reply. many thanks for all your help.

 

[LKBrwnDBA]

Depending on your DBMS is surely can be done.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: Serach for 'crosstab' or 'pivot' table.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[PHV]

If you know bp-id is only 0 or 1 then you may do conditional aggregate calculations.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[elbowe]

no bp_id can be anything up to 80 different numbers.
will look at the crosstab and pivot table ..
again thanks for help
Les

 

[elbowe]

i think that pivot and crosstab are a non starter on DB2 z/os .... but I will keep looking.
Les