summerizing multiple items at once.

Hello,

I was hoping someone here can help me out.

The Scenario is this:

We have an application here that produces general reports for our product, displays items received, the amount of error on the item, how many pages it is, the type of errors the stage in the process it’s at and the flag it’s been set to. As well as the accuracy. (there is more but these are the relative items.

Currently we display a nice grid of this information with no summary and we can get up to 5000 entries a day.

My boss has asked me to summarize it.

So he wants to know how many of each error type, how many of each stage the average of the accuracy, the total pages received, and so on. Broken down below:


CharAccur: 99,78,88,54,100
Errortype: TE,TR,TL,RR,TL
Stage: RE,ARC,AR,EX.EX
Flag: Released, Cancel, release, release, release
NumPages: 2, 3,5,2,2,
Total: 5 rows in total

So this is the data, the columns with there respective data

Now what I need to do to each.

CharAccur – Average all values
Error Type: count each error type occurrence eg. TE=1, TR=1,TL=2, RR=1
Stage: count each stage type occurrence
Flag: Count each Flag type occurrence
NumPages: Add all pages up
Total: count all returned rows.

I would like the output to be one line that has the headers like this:

CharAccur | TE | TR | TL | RR | RE and so on. And below them the values retrieved.

I know I can do this on the webpage side if I must but would be much better to do it Server side.

I tried building a whole slew of SQL Select statements for each type that got the counts and what not and then “UNION” but it does not work correctly and instead returns the first column name and a row under it for each select statement.

Any help would be appreciated we are using: MS SQL 2000

Thanks for any help.

One word: PHP