Post reply

Mon-Thu group by

  • March 31, 2011 at 8:21 am

    #237064

    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

    #1306833

    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