Summarizing multiple items at once

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Javilen
    New Member
    • Sep 2007
    • 20

    #1

    Summarizing 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.
  • azimmer
    Recognized Expert New Member
    • Jul 2007
    • 200

    #2
    Originally posted by Javilen
    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.
    The UNION way does work. Do the following:
    1. In each UNION set only one (or one type of) column filled with the right value and set the other values to NULL
    2. Add an "outer" SELECT whose FROM is the UNION-SELECT, and use max() function on each column

    Just as a quick example:
    Code:
    SELECT max(CharCurr_Avg), max(TE_Count), ..., max(Total_Count)
    FROM (
      SELECT avg(CharCurr) as CharCurr_Avg, NULL as TE_Count, ..., NULL as Total_Count
      FROM Table
      UNION
      SELECT NULL as CharCurr_Avg, sum(case when  Errortype='TE' then 1 else 0) as TE_Count, ..., NULL as Total_Count
      FROM Table
      UNION
      ...
      SELECT NULL as CharCurr_Avg, NULL as TE_Count, ..., count(*) as Total_Count
      FROM Table
    )

    Comment

    • Javilen
      New Member
      • Sep 2007
      • 20

      #3
      Thanks for the help, below is what I have placed but I get the following error:

      Server: Msg 170, Level 15, State 1, Line 8
      Line 8: Incorrect syntax near ')'.

      SELECT max(CharAcc_avg ), max(TE_Count)
      FROM (
      SELECT avg(CharAcc) as [CharAcc_Avg], NULL as [Errortype]
      FROM document
      UNION
      SELECT NULL as [CharAcc_Avg], sum(case when Errortype='TE' then 1 else 0 END ) as [TE_Count]
      FROM document
      )

      Any ideas on what I am missing?

      Comment

      • azimmer
        Recognized Expert New Member
        • Jul 2007
        • 200

        #4
        Originally posted by Javilen
        Thanks for the help, below is what I have placed but I get the following error:

        Server: Msg 170, Level 15, State 1, Line 8
        Line 8: Incorrect syntax near ')'.

        SELECT max(CharAcc_avg ), max(TE_Count)
        FROM (
        SELECT avg(CharAcc) as [CharAcc_Avg], NULL as [Errortype]
        FROM document
        UNION
        SELECT NULL as [CharAcc_Avg], sum(case when Errortype='TE' then 1 else 0 END ) as [TE_Count]
        FROM document
        )

        Any ideas on what I am missing?
        Yes, #&+#!+!, I always forget it; append an alias to the end like this:
        Code:
        SELECT max([B]myFullTable.[/B]CharAcc_avg), max([B]myFullTable.[/B]TE_Count)
        FROM (
          SELECT avg(CharAcc) as [CharAcc_Avg], NULL as [Errortype]
          FROM document
          UNION
          SELECT NULL as [CharAcc_Avg], sum(case when  Errortype='TE' then 1 else 0 END ) as [TE_Count]
          FROM document
        )[B] as myFullTable[/B]
        Sorry.

        Comment

        • Javilen
          New Member
          • Sep 2007
          • 20

          #5
          In case anyone is interested I figured it out, thanks for the help, but in the end the answer was way easier then the question, and the simple complexity of the question leads everyone who I had look at it to a much more complicated answer. Below is the solution I cam upon my self, witch works flawlessly.

          Code:
          SELECT avg(convert(Decimal,CHARACC)) as Characc_avg,
          --ERROR Type
          sum(case when  Errtype='FE' then 1 else 0 END ) as [FE_Type],
          sum(case when  Errtype='TL' then 1 else 0 END ) as [TL_Type],
          sum(case when  Errtype='FR' then 1 else 0 END ) as [FR_Type],
          sum(case when  Errtype='TE' then 1 else 0 END ) as [TE_Type],
          --Total Pages
          Sum(DocPages) as [Total Pages],
          Count(Characc) as [Total Docs]
          FROM(Table)

          Comment

          Working...