Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need help to write a DB2 query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rchandran_19

New User


Joined: 08 Mar 2005
Posts: 34

PostPosted: Wed May 28, 2008 11:29 pm    Post subject: Need help to write a DB2 query
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu May 29, 2008 12:03 am    Post subject:
Reply with quote

Hello,

You have not shown any "volume" column(s)? I would think you would want quantity or amount?
Back to top
View user's profile Send private message
rchandran_19

New User


Joined: 08 Mar 2005
Posts: 34

PostPosted: Thu May 29, 2008 2:47 am    Post subject:
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu May 29, 2008 3:17 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Thu May 29, 2008 4:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
rchandran_19

New User


Joined: 08 Mar 2005
Posts: 34

PostPosted: Fri May 30, 2008 6:50 pm    Post subject: Reply to: Need help to write a DB2 query
Reply with quote

I used Ashimer's query and it worked. Thank you very much Ashimer and Prajesh.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us