Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Aug 2011
    Posts
    32

    Question Unanswered: New to DB2: Need help in forming a query

    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...

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Aug 2011
    Posts
    32

    Unhappy

    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?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What did you actually run? I included the GROUP BY clause in the code I provided.

    Andy

  5. #5
    Join Date
    Aug 2011
    Posts
    32
    I did the exact copy, only editing the col names that werent correct....

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you post the statement?

    Andy

  7. #7
    Join Date
    Aug 2011
    Posts
    32
    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>

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  9. #9
    Join Date
    Aug 2011
    Posts
    32
    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.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  11. #11
    Join Date
    Aug 2011
    Posts
    32
    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.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    "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.

  13. #13
    Join Date
    Aug 2011
    Posts
    32
    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>

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by ab_yunus View Post
    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

  15. #15
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Also supply us with your DB2 version and OS.

    Andy

Tags for this Thread

Posting Permissions

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