Multiple counts on same table



IBM's flagship relational database management system

Multiple counts on same table

Postby GuyC » Tue Dec 06, 2011 2:16 pm

I had a question via PM. that's not what PM is for.
rekhamf wrote:Hi ,

I have 55 querys similar like this in my pgm to get different count from the table , is that possible to comibine the query in to single dml to improve effeciency ?
EXEC SQL
SELECT COUNT(*)
INTO :WS-LINEIDX4-3WEEK
FROM EMP_LOG
WHERE LOG_TS BETWEEN
TIMESTAMP(CURRENT DATE - 24 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 18 DAYS,'00:00')-1 MICROSECOND)
AND CLASS = '2'
AND PLAN = '7'
AND ACTION = 'I'
GROUP BY EMPLOY
END-EXEC

EXEC SQL
SELECT COUNT(*)
INTO :WS-LINEIDX4-2WEEK
FROM EMP_LOG
WHERE LOG_TS BETWEEN
TIMESTAMP(CURRENT DATE - 17 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 11 DAYS,'00:00')-1 MICROSECOND)
AND CLASS = '4'
AND PLAN = '7'
AND ACTION = 'I'
GROUP BY EMPLOY
END-EXEC

EXEC SQL
SELECT COUNT(*)
INTO :WS-LINEIDX4-1WEEK
FROM EMP_LOG
WHERE LOG_TS BETWEEN
TIMESTAMP(CURRENT DATE - 10 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 04 DAYS,'00:00')-1 MICROSECOND)
AND CLASS = '5'
AND PLAN = '7'
AND ACTION = 'I'
GROUP BY EMPLOY
END-EXEC
rekhamf
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Multiple counts on same table

Postby GuyC » Tue Dec 06, 2011 2:27 pm

And the answer is : yes, you can via Case and Sum

Select
,SUM(case when condition1 then 1 else 0 end) as count1
,SUM(case when condition2 then 1 else 0 end) as count2
,SUM(case when condition3 then 1 else 0 end) as count3
From Table1
where (condition1 or condition2 or condition3)
Group by Something

You should shift a little in the conditions : move the mutual ones to the where clause and combine some periods.
in your case :
select
SUM(case when LOG_TS BETWEEN TIMESTAMP(CURRENT DATE - 24 DAYS,'00:00') AND
                                          (TIMESTAMP(CURRENT DATE - 18 DAYS,'00:00')-1 MICROSECOND)
                       AND CLASS = '2'
               then 1 else 0 end ) as LINEIDX4_3WEEK ,
SUM(case when LOG_TS BETWEEN TIMESTAMP(CURRENT DATE - 17 DAYS,'00:00') AND
                                          (TIMESTAMP(CURRENT DATE - 11 DAYS,'00:00')-1 MICROSECOND)
                          AND CLASS = '4'
               then 1 else 0 end  ) as LINEIDX4_2WEEK  ,
SUM(case when LOG_TS BETWEEN TIMESTAMP(CURRENT DATE - 10 DAYS,'00:00') AND
                                            (TIMESTAMP(CURRENT DATE - 04 DAYS,'00:00')-1 MICROSECOND)
                           AND CLASS = '5'
               then 1 else 0 end ) as LINEIDX4_1WEEK
FROM EMP_LOG
 WHERE LOG_TS BETWEEN  TIMESTAMP(CURRENT DATE - 24 DAYS,'00:00') AND
                          (TIMESTAMP(CURRENT DATE - 04 DAYS,'00:00')-1 MICROSECOND)
                AND CLASS in ('2','4','5')
                AND PLAN = '7'
                AND ACTION = 'I'
GROUP BY EMPLOY
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post