|
View previous topic :: View next topic
|
| Author |
Message |
kanchanchouhan
New User
Joined: 05 Sep 2007
Posts: 11
Location: kolkata
|
|
|
|
i have an Employee table with 3 columns:emp, timestap and claim.
| Code: |
emp timestamp claim
a jan-2009 c1
b feb-2009 c1
c march-2009 c1
a feb-2010 c2
b may-2010 c2
c jan-2010 c2 |
if i need to find sum of quarterly claims on an yearly basis how can achieve that? do i need to strip the timestamp first and then on the basis of year i can get it or
| Code: |
select sum(claim)
from employee
where month in('jan-2009','feb-2009', march-2009') |
is it right? |
|
| Back to top |
|
 |
prem_ibm
New User
Joined: 15 Sep 2007
Posts: 31
Location: Chennai
|
|
|
|
Hi
Please find below query.....not tested but hope it works...where as to find the Sum of any three rows....then u need to use IN BETWEEN clause....in that u need to provide the range of columns.
SELECT SUM(CLAIM) FROM EMPLOYEE ORDER BY TIEMSTAMP WHERE COUNT(*) = '3';
it gives SUM of first three rows. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
This "solution" does not appear to address the requirement. . . |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Kanchan,
Try this,
| Code: |
SELECT Q,SUM(CLAIM) FROM (
SELECT EMPNAME, TS , CLAIM, 'Q1' AS Q FROM SESSION.EMPTAB
WHERE MONTH(TS) <= 3
UNION ALL
SELECT EMPNAME, TS , CLAIM, 'Q2' AS Q FROM SESSION.EMPTAB
WHERE MONTH(TS) > 3 AND MONTH(TS) <= 6
UNION ALL
SELECT EMPNAME, TS , CLAIM, 'Q3' AS Q FROM SESSION.EMPTAB
WHERE MONTH(TS) > 6 AND MONTH(TS) <= 9
UNION ALL
SELECT EMPNAME, TS , CLAIM, 'Q4' AS Q FROM SESSION.EMPTAB
WHERE MONTH(TS) > 9 AND MONTH(TS) <= 12
) AS TAB
GROUP BY Q |
Thanks You,
Sushanth |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| sushanth bobby wrote: |
Kanchan,
Try this,
| Code: |
SELECT Q,SUM(CLAIM) FROM (
SELECT EMPNAME, TS , CLAIM, 'Q1' AS Q FROM SESSION.EMPTAB
WHERE MONTH(TS) <= 3
UNION ALL
SELECT EMPNAME, TS , CLAIM, 'Q2' AS Q FROM SESSION.EMPTAB
WHERE MONTH(TS) > 3 AND MONTH(TS) <= 6
UNION ALL
SELECT EMPNAME, TS , CLAIM, 'Q3' AS Q FROM SESSION.EMPTAB
WHERE MONTH(TS) > 6 AND MONTH(TS) <= 9
UNION ALL
SELECT EMPNAME, TS , CLAIM, 'Q4' AS Q FROM SESSION.EMPTAB
WHERE MONTH(TS) > 9 AND MONTH(TS) <= 12
) AS TAB
GROUP BY Q |
Thanks You,
Sushanth |
besides disregarding different years (easily solved), You do realize that you probably have 4 tablescans ?
better would be to use year(ts) * 10 + int((month(ts) - 1) / 4 ) + 1 as Q |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Nice Solution GuyC.
Sushanth |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
ty,
just noticed it should be /3 instead of /4 |
|
| Back to top |
|
|
|
|
