-
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
-
use group by plus sum plus decode
Last edited by pando; 03-23-2006 at 03:49 AM.
-
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.
-
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')
-
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')
/
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|