|
View previous topic :: View next topic
|
| Author |
Message |
rchandran_19
New User
Joined: 08 Mar 2005
Posts: 34
|
|
|
|
Could someone please help me to write a DB2 query.
There is a table A with fields
Entity, Type and Date.
Table A
----------------------------
| Entity |Type| Date |
----------------------------
| CAN | P | 05/01/2008 |
| USA | P | 05/05/2008 |
| FRA | C | 05/06/2008 |
| CAN | D | 05/07/2008 |
| CAN | C | 05/07/2008 |
| USA | D | 05/05/2008 |
| CAN | P | 05/05/2008 |
| USA | P | 05/02/2008 |
| FRA | P | 05/05/2008 |
|...
|...
|...
|...
Please help me to write a query to find:
Average daily volume of TYPE P totals for Entity CAN for a date range of 05/01/2008 to 05/07/2008. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
You have not shown any "volume" column(s)? I would think you would want quantity or amount? |
|
| Back to top |
|
|
rchandran_19
New User
Joined: 08 Mar 2005
Posts: 34
|
|
|
|
Hello Scherrer,
There is no column "volume" in this table.
For example if there are
total 10 'P' types for entity 'CAN' on 05/01/2008
total 20 'P' types for entity 'CAN' on 05/02/2008
total 30 'P' types for entity 'CAN' on 05/03/2008
total 40 'P' types for entity 'CAN' on 05/04/2008
total 50 'P' types for entity 'CAN' on 05/05/2008
total 60 'P' types for entity 'CAN' on 05/06/2008
total 70 'P' types for entity 'CAN' on 05/07/2008
i need the query to find the average of these totals (10+20+30+40+50+60+70)/7 = 40
Please help |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Chandra,
Pls try this ..i have not tested this but hope this works ..
| Code: |
SELECT COUNT(ENTITY) / COUNT(DISTINCT DATE) AS AVERAGE
FROM SESSION.EMP WHERE
ENTITY ='CAN' AND TYPE ='P'
AND DATE
BETWEEN '05/01/2008' AND '05/07/2008' GROUP BY ENTITY;
|
|
|
| Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006
Posts: 133
Location: India
|
|
|
|
Hi rchandran & Ashimer,
Will the below query work as a Genereal solution to the problem?
rchandran, Can you test this out against ur table data?
| Code: |
SELECT SUM(NOFP)/COUNT(DT1),ENTITY,TYPE FROM(
SELECT COUNT(1) AS NOFP,ENTITY,TYPE,COUNT(DISTINCT DATE) AS DT1
FROM TBL.TEST2
GROUP BY ENTITY,TYPE,DATE) AS TEMP
GROUP BY ENTITY,TYPE |
Might need some tuning and tweeking!
Corrections are welcome
Thanks,
Prajesh |
|
| Back to top |
|
|
rchandran_19
New User
Joined: 08 Mar 2005
Posts: 34
|
|
|
|
| I used Ashimer's query and it worked. Thank you very much Ashimer and Prajesh. |
|
| Back to top |
|
|
|
|
