IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Select two different counts from SQL and its differnce


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Fri Apr 07, 2023 12:29 pm
Reply with quote

Hi Team , I am pulling the two different counts from the same table and gets the difference for the same . However the order of rows and the difference is not as expected .
Code:


SELECT
distinct date(connected_at) as connected_date,
date(ended_at)  as end_date,
     CASE
         WHEN type = 'A' and  Status = 'finished'
             THEN COUNT(*) else 0
     END as total
    ,
     
  CASE
         WHEN type = 'B' and Status = 'finished'
             THEN COUNT(*) else 0
     END as total_va
    ,
   
  COUNT(CASE WHEN type = 'A'      and  Status = 'finished' then 1 END)  - 
  COUNT(CASE  WHEN type = 'B' and  Status = 'finished' then 1
             END)  AS DIFF

FROM TEST_CALL_TABLE
where connected_at is not null
and ended_at is not null
group by 1,2,type,Status
order by 1,2
;



Actual result :-
Code:

connected_date   end_date   total   total_va   DIFF
16-03-2023   16-03-2023   2   0   2
16-03-2023   16-03-2023   0   2   -2
17-03-2023   17-03-2023   24   0   24
17-03-2023   17-03-2023   0   24   -24
20-03-2023   20-03-2023   72   0   72



Expected Result :-

Code:


connected_date   end_date   total   total_va   DIFF
16-03-2023   16-03-2023   2   2   0
17-03-2023   17-03-2023   24   24   0
20-03-2023   20-03-2023   72   72   0





Can any one please help me on this .

Thanks
Balaji K
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Fri Apr 07, 2023 6:58 pm
Reply with quote

Try this.
I don't have access to DB2 right now, but I hope it should work icon_confused.gif

A small trick not to forget: take into account possible NULL counter values for some date ranges...

Code:
WITH T(c_date, e_date, type, tot) AS
( SELECT
     date(connected_at),
     date(ended_at),
     type,
     count(*)
  FROM TEST_CALL_TABLE
  WHERE connected_at is not null
    and ended_at     is not null
    and Status     = 'finished'
  group by 1, 2, 3
)
SELECT
   T0.c_date                         as connected_date,
   T0.e_date                         as end_date,
   nvl(TA.tot, 0)                    as total,
   nvl(TB.tot, 0)                    as total_va,
  (nvl(TA.tot, 0) - nvl(TB.tot, 0) ) as diff
FROM (
  // get full list of dates for both type 'A', and 'B'
  (SELECT DISTINCT c_date, e_date
   FROM T WHERE type IN ('A', 'B')          ) T0
LEFT JOIN
  // join with type 'A' counter values
  (SELECT * FROM T WHERE type = 'A'         ) TA
  ON T0.c_date = TA.c_date AND T0.e_date = TA.e_date
LEFT JOIN   
  // join with type 'B' counter values
  (SELECT * FROM T WHERE type = 'B'         ) TB
  ON T0.c_date = TB.c_date AND T0.e_date = TB.e_date
)
ORDER BY 1, 2
;
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Fri Apr 07, 2023 7:15 pm
Reply with quote

Expected keyword AS but got "("

"WITH T(c_date, e_date, type, tot) AS" - Please help
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Fri Apr 07, 2023 8:23 pm
Reply with quote

balaji81_k wrote:
Expected keyword AS but got "("
"WITH T(c_date, e_date, type, tot) AS" - Please help

I do not have access to DB2 right now.

You MUST try to test it yourself, and FIX syntax errors by yourself.
Try some experiments: which '(' is not accepted?

Maybe, a space is required
Code:
WITH T (c_date, e_date, type, tot) AS
or whatever else?

Do something, not only copy-and-paste!!!
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Fri Apr 07, 2023 9:35 pm
Reply with quote

hi , i am working on the correcting the few info in the sample provided.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Sun Apr 09, 2023 9:24 pm
Reply with quote

Based on your message, your DB2 version does not accept field list in that WITH clause.

Try the same without that list:
Code:
WITH T AS
( SELECT
     date(connected_at)      as c_date,
     date(ended_at)          as e_date,
     type,
     count(*)                as tot
  FROM TEST_CALL_TABLE
  WHERE connected_at is not null
    and ended_at     is not null
    and Status     = 'finished'
  group by 1, 2, 3
)
SELECT
   T0.c_date                         as connected_date,
   T0.e_date                         as end_date,
   nvl(TA.tot, 0)                    as total,
   nvl(TB.tot, 0)                    as total_va,
  (nvl(TA.tot, 0) - nvl(TB.tot, 0) ) as diff
FROM (
  // get full list of dates for both type 'A', and 'B'
  (SELECT DISTINCT c_date, e_date
   FROM T WHERE type IN ('A', 'B')          ) T0
LEFT JOIN
  // join with type 'A' counter values
  (SELECT * FROM T WHERE type = 'A'         ) TA
  ON T0.c_date = TA.c_date AND T0.e_date = TA.e_date
LEFT JOIN   
  // join with type 'B' counter values
  (SELECT * FROM T WHERE type = 'B'         ) TB
  ON T0.c_date = TB.c_date AND T0.e_date = TB.e_date
)
ORDER BY 1, 2
;
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Mon Apr 10, 2023 7:48 pm
Reply with quote

Thankyou Sir. It is working fine . Yes my DB2 version doesn;t allow the list. It is working as per my expectation. Many thanks !
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Dynamically pass table name to a sele... DB2 2
No new posts SELECT from data change table DB2 5
No new posts Select a DB2 value in a specific deci... DB2 4
No new posts How can I select certain file dependi... JCL & VSAM 12
This topic is locked: you cannot edit posts or make replies. How can I select certain file dependi... Compuware & Other Tools 1
Search our Forums:

Back to Top