New to DB2: Need help in forming a query

ab_yunus · #1 (**permalink**) 08-17-11, 12:32

Hi,
I have a table where in patient records are sent in on a daily basis and it has to be printed by our staffs. I have different branches and I want to see which branch has printed the records and which have not. How many have been printed by a branch level. So I have almost formed the query, but still it is not fitting rightly. I am new to DB2 and please help me out.

the query should be like "Count the rows of batch_id and batch_print_time (both are unique to each record) for the date of batch_date ='2011-08-16' and order by branch_id.

I tried:

Code:

Select branch_id, count(batch_id, batch_print_time) from batch_records_print table where batch_print_date ='2011-08-16' with ur;

but it is throwing error.

if I take all the records from the table(for the particular date) and paste it in a excel, to get the result all I have to do is,
1. do a subtotal ON EACH CHANGE in branch_id
2. use function COUNT
3. add subtotal to batch_id and batch_print_time

and it gives me the result of each branch, how many records were sent, how many were printed. So how do i obtain this result from a db2 query itself? Please help me. Thank you...

ARWinner · #2 (**permalink**) 08-17-11, 12:54

Without seeing you data and what exactly you want in return, I think what you want is this:

Code:

Select branch_id, count(*) from batch_records_print table where batch_print_date ='2011-08-16' group by branch_id with ur;

Andy

ab_yunus · #3 (**permalink**) 08-17-11, 13:22

Hi Andy,

Thanks for trying to help me out. But your query returned me the following error.

<eb1>SQL0122N A SELECT statement with no GROUP BY clause contains a column name and a column function in the SELECT clause, or a column name is contained in the SELECT clause but not in the GROUP BY clause. SQLSTATE=42803

What does it mean?

ARWinner · #4 (**permalink**) 08-17-11, 13:25

What did you actually run? I included the GROUP BY clause in the code I provided.

Andy

ab_yunus · #5 (**permalink**) 08-17-11, 13:28

I did the exact copy, only editing the col names that werent correct....

ARWinner · #6 (**permalink**) 08-17-11, 13:29

Can you post the statement?

Andy

ab_yunus · #7 (**permalink**) 08-17-11, 13:42

ok..
this is the actual query:

Code:

select sgn_uid, count (*) from ps_batch_signs where SGN_ST_DT='2011-08-16' group by unit_id with ur;

and the result was

Code:

<eb1>SQL0122N  A SELECT statement with no GROUP BY clause contains a column name and a column function in the SELECT clause, or a column name is contained in the SELECT clause but not in the GROUP BY clause.  SQLSTATE=42803

State:42803,Native:-122,Origin:[IBM][CLI Driver][DB2]</eb1>

ARWinner · #8 (**permalink**) 08-17-11, 13:45

The GROUP BY columns must match the columns in the select:

Code:

select sgn_uid, count (*) from ps_batch_signs where SGN_ST_DT='2011-08-16' group by sgn_uid with ur;

Andy

ab_yunus · #9 (**permalink**) 08-17-11, 14:08

Hi Andy,

I did not get the results I wanted. I will give you some data so that You can understand. I am sorry I dint do it early.

"Count the rows of batch_id and batch_print_time (both are unique to each record) for the date of batch_date ='2011-08-16' and order by branch_id.

unit_id ----record_print_no---- printed_time
7001 804483 09:11.6
7001 804484 08:22.7
7001 804485 09:11.6
7001 804486 0
7001 804487 0
7001 804488 0
7001 804456 0
7001 804457 0
7004 771202 29:48.5
7004 771153 46:58.8
7004 771154 47:38.0
7004 771155 0
7004 771203 0
7004 771204 0
7004 771171 28:26.4
7004 771172 28:26.4
7005 637632 23:19.0
7005 637633 0
7005 637644 0
7005 637645 19:01.4
7005 637646 20:11.5
7006 598201 42:03.1
7006 598202 42:03.1
7006 598203 42:03.1
7006 598204 36:03.0
7006 598205 36:35.9
7006 598206 37:27.4

now after the query is run I should get a count of the number of times the print_time row is updated with a time (it will be '0' if it is not printed). The record_print_no will also be unique. So the results of the query should be

7001 8 3
7004 8 5
7005 5 3
7006 6 6

Hope you understand my criteria now?? The total number of records counted using the record_print_no column and the number of records printed using the printed_time column. The batch_date (2011-08-16) would be same for all stores.

ARWinner · #10 (**permalink**) 08-17-11, 14:26

I think I have it now:

Code:

select unit_id, count (distinct record_print_no) as num_records,count(distinct (nullif(printed_time,'0')) as num_printed from ps_batch_signs where SGN_ST_DT='2011-08-16' group by unit_id with ur;

Andy

ab_yunus · #11 (**permalink**) 08-17-11, 14:47

Hi Andy,

By running your query, I got the below result.

UNIT_ID 2
7001 28
7002 17
7003 9
7004 39
7005 12
7006 12
7007 13
7008 11
7009 19
7011 10
7012 14
7013 15
7017 16
7021 11
7022 12
7023 23
7024 8
7025 15
7026 11
7027 33

what is column 2? I need one more column also, if you look the result I showed you in t example. Can you check. Thanks a ton bro.

tonkuma · #12 (**permalink**) 08-17-11, 15:10

Quote:

"Count the rows of batch_id and batch_print_time (both are unique to each record) for the date of batch_date ='2011-08-16' and order by branch_id.

1) There are a little differences between column names in the data and the names in the description.
Also, the names different from sample queries and descriptions in your former posts.

2-1) No batch_id(to be counted) in the data.
2-2) No branch_id(to be orderd) in the data.

3) What is the name of the table? I saw at least two names in your earler queries.

4) What is the datatype of batch_date? Isn't it DATE?

Anyhow, from your data and expected result,
your query might look like...

Code:

------------------------------ Commands Entered ------------------------------
WITH
 your_data(unit_id , record_print_no , printed_time , batch_date) AS (
VALUES
  (7001 , 804483 , '09:11.6' , '2011-08-16')
, (7001 , 804484 , '08:22.7' , '2011-08-16')
, (7001 , 804485 , '09:11.6' , '2011-08-16')
, (7001 , 804486 , '0'       , '2011-08-16')
, (7001 , 804487 , '0'       , '2011-08-16')
, (7001 , 804488 , '0'       , '2011-08-16')
, (7001 , 804456 , '0'       , '2011-08-16')
, (7001 , 804457 , '0'       , '2011-08-16')
, (7004 , 771202 , '29:48.5' , '2011-08-16')
, (7004 , 771153 , '46:58.8' , '2011-08-16')
, (7004 , 771154 , '47:38.0' , '2011-08-16')
, (7004 , 771155 , '0'       , '2011-08-16')
, (7004 , 771203 , '0'       , '2011-08-16')
, (7004 , 771204 , '0'       , '2011-08-16')
, (7004 , 771171 , '28:26.4' , '2011-08-16')
, (7004 , 771172 , '28:26.4' , '2011-08-16')
, (7005 , 637632 , '23:19.0' , '2011-08-16')
, (7005 , 637633 , '0'       , '2011-08-16')
, (7005 , 637644 , '0'       , '2011-08-16')
, (7005 , 637645 , '19:01.4' , '2011-08-16')
, (7005 , 637646 , '20:11.5' , '2011-08-16')
, (7006 , 598201 , '42:03.1' , '2011-08-16')
, (7006 , 598202 , '42:03.1' , '2011-08-16')
, (7006 , 598203 , '42:03.1' , '2011-08-16')
, (7006 , 598204 , '36:03.0' , '2011-08-16')
, (7006 , 598205 , '36:35.9' , '2011-08-16')
, (7006 , 598206 , '37:27.4' , '2011-08-16')
)
SELECT unit_id
     , COUNT(*) AS number_of_rows
     , COUNT( NULLIF(printed_time , '0') ) AS count_printed_time
 FROM  your_data
 WHERE batch_date = '2011-08-16'
 GROUP BY
       unit_id
 ORDER BY
       unit_id
;
------------------------------------------------------------------------------

UNIT_ID     NUMBER_OF_ROWS COUNT_PRINTED_TIME
----------- -------------- ------------------
       7001              8                  3
       7004              8                  5
       7005              5                  3
       7006              6                  6

  4 record(s) selected.

ab_yunus · #13 (**permalink**) 08-17-11, 15:20

Hi Tonkuma,

from your query, it throws me the below error?

Code:

<eb1>SQL0180N  The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007

State:22007,Native:-180,Origin:[IBM][CLI Driver][DB2]</eb1>

ARWinner · #14 (**permalink**) 08-17-11, 15:32

Quote:

Originally Posted by ab_yunus

Hi Andy,

By running your query, I got the below result.

UNIT_ID 2
7001 28
7002 17
7003 9
7004 39
7005 12
7006 12
7007 13
7008 11
7009 19
7011 10
7012 14
7013 15
7017 16
7021 11
7022 12
7023 23
7024 8
7025 15
7026 11
7027 33

what is column 2? I need one more column also, if you look the result I showed you in t example. Can you check. Thanks a ton bro.

You are copying/modifying the query incorrectly. The query I provided had 3 columns and all were named. I suggest that from now on when you post a result, that you also post the exact query YOU ran.

Andy

ARWinner · #15 (**permalink**) 08-17-11, 15:33

Also supply us with your DB2 version and OS.

Andy

Internet Services

Web Development

Digital Marketing

Consumer Tech