March 31, 2011 at 8:21 am
Hi i have a table with "Class", "Dates" and "Copies". Now i want sum of all copies for a distinct class for all Mondays, Tuesdays, Wednesdays & Thursdays.
I wrote a code
SELECT
substr((upper(to_char(trunc(b.date, 'iw') ,'day'))),1,1) M
,substr((upper(to_char(trunc(b.date, 'iw')+1 ,'day'))),1,1) T
,substr((upper(to_char(trunc(b.date, 'iw')+2 ,'day'))),1,1) W
,substr((upper(to_char(trunc(b.date, 'iw')+3 ,'day'))),1,1) H
,sum(b.copies)Total_ Copies
,b.class
FROM circ.adi a
,circ_rpt.vw_draw_drw_draw_type b
WHERE a.adi = b.adi
AND A.PUBLICATION = B.PUBLICATION
group by
substr((upper(to_char(trunc(b.date, 'iw') ,'day'))),1,1)
,substr((upper(to_char(trunc(b.date, 'iw')+1 ,'day'))),1,1)
,substr((upper(to_char(trunc(b.date, 'iw')+2 ,'day'))),1,1)
,substr((upper(to_char(trunc(b.date, 'iw')+3 ,'day'))),1,1)
,b.class
But apparently this is giving sum of all Monday-Thursday 's. I just need some idea to start. Thank you.
Edited by: varun on Mar 31, 2011 7:18 AM
April 3, 2011 at 3:58 pm
Filter out unwanted days of the week on WHERE clause.
Use GROUP BY to group by Class, Day-of-the-week.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply