Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2012
    Posts
    12

    Unanswered: Status with a group by

    select variable_a, count(*) from table group by variable a

    The results are:

    Variable_a 2
    ----------- -----------
    1 7
    3 55
    4 2

    3 record(s) selected.


    What I am trying to do, ultimately, is to be able to use the query in order to look at how many errors I have. That would be 7 currently. Bascially, I want to turn this result in to a pivot table and report off of it. I have two tables, this one plus a history table.

    The query works on both tables currently. But, I cannot figure out how to create the pivot table. I want to use dates as the first column, the status as the 2 to 14 columns.

    First, is this doable. Secondly, how? Thirdly...any good book suggestions?

    Thanks,

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does the Variable_A column have a small, fixed number of values or is it variable? If the values were 1 through 13, then you could do something like
    Code:
    SELECT DateColumn
    ,  Sum(CASE WHEN  1 = Variable_A THEN 1 END) AS  1
    ,  Sum(CASE WHEN  2 = Variable_A THEN 1 END) AS  2
    ,  Sum(CASE WHEN  3 = Variable_A THEN 1 END) AS  3
    ,  Sum(CASE WHEN  4 = Variable_A THEN 1 END) AS  4
    ,  Sum(CASE WHEN  5 = Variable_A THEN 1 END) AS  5
    ,  Sum(CASE WHEN  6 = Variable_A THEN 1 END) AS  6
    ,  Sum(CASE WHEN  7 = Variable_A THEN 1 END) AS  7
    ,  Sum(CASE WHEN  8 = Variable_A THEN 1 END) AS  8
    ,  Sum(CASE WHEN  9 = Variable_A THEN 1 END) AS  9
    ,  Sum(CASE WHEN 10 = Variable_A THEN 1 END) AS 10
    ,  Sum(CASE WHEN 11 = Variable_A THEN 1 END) AS  11
    ,  Sum(CASE WHEN 12 = Variable_A THEN 1 END) AS  12
    ,  Sum(CASE WHEN 13 = Variable_A THEN 1 END) AS  13
       FROM myTable
       GROUP BY DateColumn
       ORDER BY DateColumn;
    Note that in 99% of the cases that I've seen, this is a VERY bad idea. If you need to manage presentation (like a pivot), that presentation ought to be done in the presentation layer rather than in the database, the application server, or the kitchen sink... Doing work in the wrong place almost always makes more work!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2012
    Posts
    12
    Pat,

    Thanks for your reply. I do get that it's not optimal. But, this database does not have an app server, etc. It's for a printing application. I'm just trying to monitor the job status to keep track of problems.

    So, in that vain, I created a script that runs the code you gave me. I'm working on getting it to work. Here is my error messages below and a copy of the running code.

    # ./test.sh

    Database Connection Information

    Database server = DB2/LINUXX8664 9.7.1
    SQL authorization ID = DB2INST1
    Local database alias = SPOOLER


    SQL0104N An unexpected token "1" was found following "ableA THEN 1 END) AS".
    Expected tokens may include: "<space>". SQLSTATE=42601

    ---------------------------------------------------------------------------------

    # cat test1.db2
    connect to spooler;

    SELECT job_status
    , Sum(CASE WHEN 1 = Variable_A THEN 1 END) AS 1
    , Sum(CASE WHEN 2 = Variable_A THEN 1 END) AS 2
    , Sum(CASE WHEN 3 = Variable_A THEN 1 END) AS 3
    , Sum(CASE WHEN 4 = Variable_A THEN 1 END) AS 4
    , Sum(CASE WHEN 5 = Variable_A THEN 1 END) AS 5
    , Sum(CASE WHEN 6 = Variable_A THEN 1 END) AS 6
    , Sum(CASE WHEN 7 = Variable_A THEN 1 END) AS 7
    , Sum(CASE WHEN 8 = Variable_A THEN 1 END) AS 8
    , Sum(CASE WHEN 9 = Variable_A THEN 1 END) AS 9
    , Sum(CASE WHEN 10 = Variable_A THEN 1 END) AS 10
    , Sum(CASE WHEN 11 = Variable_A THEN 1 END) AS 11
    , Sum(CASE WHEN 12 = Variable_A THEN 1 END) AS 12
    , Sum(CASE WHEN 13 = Variable_A THEN 1 END) AS 13
    FROM spoolibm.jobs
    GROUP BY job_status
    ORDER BY job_status;



    Your help is appreciated.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what comes after the AS has to be an identifier, and in this case those numbers have to be delimited
    Code:
    , Sum(CASE WHEN 1 = Variable_A THEN 1 END) AS "1"
    , Sum(CASE WHEN 2 = Variable_A THEN 1 END) AS "2"
    ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2012
    Posts
    12
    SQL0206N "VARIABLE_A" is not valid in the context where it is used.
    SQLSTATE=42703


    Thanks. That has gotten me further. I'm working on the above error message. Do I need to declare the variable?

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by dw29a View Post
    SQL0206N "VARIABLE_A" is not valid in the context where it is used.
    That is meant to be the column name of your table; see your initial example!
    So replace it with the "real" column name that contains the labels 1, 2, etc.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Jun 2012
    Posts
    12
    Okay...that was a duh moment. Thanks Peter. I'm not a DBA...can you tell? I'm just a Unix guy that they gave the DB keys too. LOL

    That plus an CAST as integer got what I was looking for. Thanks very much.

    Now, I just have to toy around with it a bit more in order to get it to look a bit different.

    Thanks very much.
    Last edited by dw29a; 07-02-12 at 12:42.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry about the confusion! That was "air code" written on a cell phone, I ought to have checked it more thoroughly when I got to a computer but got distracted by an especially horrid weekend.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2012
    Posts
    12
    No problem at all. I appreciate your time.

    I do have one last question about this topic, I hope....

    I have the status printing out with the date in the first column. I would like to have all status' for a given date in one row. Right now, each status gives me one row...if that makes any sense. I do believe that I'm almost there.

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by dw29a View Post
    That plus an CAST as integer got what I was looking for.
    No need to cast as integer: suppose the 5 values in your column keycol are "A" up to "E"; then the SQL statement would become:
    Code:
    SELECT DateColumn
    ,      sum(CASE WHEN keycol = 'A' THEN 1 ELSE 0 END) AS A
    ,      sum(CASE WHEN keycol = 'B' THEN 1 ELSE 0 END) AS B
    ,      sum(CASE WHEN keycol = 'C' THEN 1 ELSE 0 END) AS C
    ,      sum(CASE WHEN keycol = 'D' THEN 1 ELSE 0 END) AS D
    ,      sum(CASE WHEN keycol = 'E' THEN 1 ELSE 0 END) AS E
    FROM   myTable
    GROUP BY DateColumn
    ORDER BY DateColumn;
    or, for a "grand total" of the 5 categories, just:
    Code:
    SELECT sum(CASE WHEN keycol = 'A' THEN 1 ELSE 0 END) AS A
    ,      sum(CASE WHEN keycol = 'B' THEN 1 ELSE 0 END) AS B
    ,      sum(CASE WHEN keycol = 'C' THEN 1 ELSE 0 END) AS C
    ,      sum(CASE WHEN keycol = 'D' THEN 1 ELSE 0 END) AS D
    ,      sum(CASE WHEN keycol = 'E' THEN 1 ELSE 0 END) AS E
    FROM   myTable
    (The "ELSE 0" makes sure you will see zeroes instead of NULLs in "empty" output cells.)
    Last edited by Peter.Vanroose; 07-02-12 at 15:07.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Jun 2012
    Posts
    12
    Thanks, but that does not seem to be doing it. I'm still getting:

    1 JOB_STATUS Unknown Queued Printing Printed Error PP PCE DNS Cancel Cancelled Submission Sent Terminated
    ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
    7/2/2012 1 0 3 0 0 0 0 0 0 0 0 0 0 0
    7/2/2012 2 0 0 1 0 0 0 0 0 0 0 0 0 0
    7/2/2012 3 0 0 0 49 0 0 0 0 0 0 0 0 0


    What I am, ultimately, looking for:


    1 JOB_STATUS Unknown Queued Printing Printed Error PP PCE DNS Cancel Cancelled Submission Sent Terminated
    ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
    7/2/2012 1 0 3 1 49 5 0 0 0 0 0 0 0 0


    I might be making this stupidly complex.

  12. #12
    Join Date
    Jun 2012
    Posts
    12
    I figured it out!!!!!!!!! Yeah me!!! LOL

    I changed the order by and group by Date only. Works like a champ.

    I knew I was over thinking it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •