Home » SQL & PL/SQL » SQL & PL/SQL » Difference between sum(decode) and count (PL/SQL)
Difference between sum(decode) and count [message #674707] |
Tue, 12 February 2019 03:02 |
|
klaartje
Messages: 3 Registered: February 2019
|
Junior Member |
|
|
In my query, I'm showing the sum of the amount (in €)of pieces sold in general, and then separated by year (2016, 2017, 2018). I want to do the same with the amount of pieces sold in general, and then separated by year (2016, 2017, 2018). If I use the same clause, I get the same result everywhere (for example: if the total amount of pieces in general is 89, I get 89 as the amount of pieces sold in 2016, 89 as the amount of pieces sold in 2017, and 89 as the amount of pieces sold in 2018. How can I solve this?
SELECT DRCENTR.DECARNR, DRCENTR.DESUFCE, DRCENTR.DEGENRE, DRGENRE.DEGENGR, Sum(DRCENTR.DEVRKPR) AS "Totaal verkoopprijs", Count(DRCENTR.DECARNR) AS "Aantal stuks",
Sum(DECODE(TO_CHAR(DRCENTR.DEDATUT,'YYYY'),'2016',DRCENTR.DEVRKPR,0)) AS "2016 ",
Sum(DECODE(TO_CHAR(DRCENTR.DEDATUT,'YYYY'),'2017',DRCENTR.DEVRKPR,0)) AS "2017 ",
Sum(DECODE(TO_CHAR(DRCENTR.DEDATUT,'YYYY'),'2018',DRCENTR.DEVRKPR,0)) AS "2018 ",
Count(DECODE(TO_CHAR(DRCENTR.DEDATUT,'YYYY'),'2016',DRCENTR.DECARNR,0)) AS "2016 ",
Count(DECODE(TO_CHAR(DRCENTR.DEDATUT,'YYYY'),'2017',DRCENTR.DECARNR,0)) AS "2017 ",
Count(DECODE(TO_CHAR(DRCENTR.DEDATUT,'YYYY'),'2018',DRCENTR.DECARNR,0)) AS "2018 "
FROM BHT.DRCENTR DRCENTR, BHT.DRGENRE DRGENRE
WHERE DRCENTR.DEGENRE = DRGENRE.DEGENRE AND ((DRCENTR.DESTATU='U') AND (DRCENTR.DEDATUT Between '01-JAN-16' And '31-DEC-18') AND (DRCENTR.DEOMGEV<>'S' And DRCENTR.DEOMGEV<>'I' And DRCENTR.DEOMGEV<>'R' And DRCENTR.DEOMGEV<>'T' And DRCENTR.DEOMGEV<>'D') AND (DRCENTR.DECARNR Not Like '*%' And DRCENTR.DECARNR Not Like '!%' And DRCENTR.DECARNR Not Like '$%') AND (DRCENTR.DEGENRE<>'PPP' And DRCENTR.DEGENRE<>'D74' And DRCENTR.DEGENRE<>'D73'))
GROUP BY DRCENTR.DECARNR, DRCENTR.DESUFCE, DRCENTR.DEGENRE, DRGENRE.DEGENGR
HAVING (DRGENRE.DEGENGR='NECKLACES')
ORDER BY Sum(DRCENTR.DEVRKPR) DESC
|
|
|
|
|
Re: Difference between sum(decode) and count [message #674717 is a reply to message #674707] |
Tue, 12 February 2019 07:41 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
The code is not good.
1. Converting a date to character to compare it to a character string is wrong. TO_CHAR(DRCENTR.DEDATUT,'YYYY') should be written as trunc(DRCENTR.DEDATUT,'YYYY') to compare it to the year.
2. '01-JAN-16' And '31-DEC-18' are strings, not dates, so if DRCENTR.DEDATUT is truly a date, then this is wrong, and if it a varchar2, then the string '02-JAN-11' falls between it.
|
|
|
Re: Difference between sum(decode) and count [message #674718 is a reply to message #674710] |
Tue, 12 February 2019 07:44 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
There is zero need to check for each year. if you look at the following example you do not have to check for a specific year
SELECT Owner,
TO_CHAR(Created, 'YYYY') Year,
Object_type,
COUNT(*) Cnt
FROM All_objects
GROUP BY Owner, TO_CHAR(Created, 'YYYY'), Object_type
ORDER BY TO_CHAR(Created, 'YYYY'), Owner, Object_type;
|
|
|
Goto Forum:
Current Time: Sun Jun 16 09:24:20 CDT 2024
|