Query help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Query help

  1. #1
    Join Date
    Mar 2006
    Posts
    5

    Question Query help

    HI,


    I have a table called dload and i want to extract all the count according to month and also acc to feedback whether yes or no, output has to look something like this.

    Month Dload Yes No

    Oct-05 166 22 72
    Nov-05 167 12 67
    Dec-05 257 48 95


    I'm able to extract month and total count, is it possible to extract the other two in a single query, if so help me.

    Also suggest me some good learning material to learn query writing in oracle as i'm new

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    use group by plus sum plus decode
    Last edited by pando; 03-23-2006 at 02:49 AM.

  3. #3
    Join Date
    Mar 2006
    Posts
    5
    I forgot to post my query

    SELECT SUBSTR(TO_CHAR(TRUNC(d.DOWNLOADDATE,'MM')),4,6), COUNT(*) FROM DOWNLOAD d
    WHERE d.DOWNLOADDATE BETWEEN TO_DATE('07/01/2005','mm/dd/yyyy') AND TO_DATE(TO_CHAR(SYSDATE,'mm/dd/yyyy'),'mm/dd/yyyy')
    GROUP BY TRUNC(d.DOWNLOADDATE,'MM')
    ORDER BY TRUNC(d.DOWNLOADDATE,'MM')

    this is my query.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Code:
    SELECT 
    	SUBSTR(TO_CHAR(TRUNC(d.DOWNLOADDATE,'MM')),4,6),
    	COUNT(*) FROM DOWNLOAD d,
    	SUM(DECODE(FEEDBACK, 'Y', 1, 'N', 0)) FEED_BACK_Y,
    	SUM(DECODE(FEEDBACK, 'Y', 0, 'N', 1)) FEED_BACK_N
    WHERE d.DOWNLOADDATE BETWEEN TO_DATE('07/01/2005','mm/dd/yyyy') AND TO_DATE(TO_CHAR(SYSDATE,'mm/dd/yyyy'),'mm/dd/yyyy')
    GROUP BY TRUNC(d.DOWNLOADDATE,'MM')
    ORDER BY TRUNC(d.DOWNLOADDATE,'MM')

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Like this?

    Code:
    SELECT TO_CHAR(DOWNLOADDATE,'MON-YYYY') month, COUNT(*) dload,
           SUM(DECODE(acc, 'YES', 1, 0)) YES,
           SUM(DECODE(acc, 'NO',  1, 0)) NO,
      FROM DOWNLOAD
     WHERE DOWNLOADDATE 
                BETWEEN TO_DATE('07/01/2005','mm/dd/yyyy')
                    AND SYSDATE
     GROUP BY TO_CHAR(DOWNLOADDATE,'MON-YYYY')
     ORDER BY TO_CHAR(DOWNLOADDATE,'MON-YYYY')
    /
    this space intentionally left blank

  6. #6
    Join Date
    Mar 2006
    Posts
    5
    Thanks gandolf989, ur code gave me wat i needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width