Results 1 to 12 of 12
Thread: Status with a group by

062912, 15:57 #1Registered User
 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,

062912, 16:47 #2Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Does 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;
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

070212, 10:52 #3Registered User
 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.

070212, 11:08 #4SQL Consultant
 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" ...

070212, 11:30 #5Registered User
 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?

070212, 12:31 #6Registered User
 Join Date
 Sep 2004
 Location
 Belgium
 Posts
 1,126
_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/

070212, 12:36 #7Registered User
 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; 070212 at 12:42.

070212, 13:25 #8Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Sorry 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.
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

070212, 14:33 #9Registered User
 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.

070212, 14:59 #10Registered User
 Join Date
 Sep 2004
 Location
 Belgium
 Posts
 1,126
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;
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
Last edited by Peter.Vanroose; 070212 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/

070212, 15:19 #11Registered User
 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.

070212, 16:35 #12Registered User
 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.