Home » SQL & PL/SQL » SQL & PL/SQL » Difference between sum(decode) and count (PL/SQL)
Difference between sum(decode) and count Tue, 12 February 2019 03:02
 klaartje Messages: 3Registered: 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 #674709 is a reply to message #674707] Tue, 12 February 2019 04:17
 Michel Cadot Messages: 66249Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
If you don't know how to format the code, learn it using SQL Formatter.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Difference between sum(decode) and count [message #674710 is a reply to message #674707] Tue, 12 February 2019 04:21
 Michel Cadot Messages: 66249Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

I don't know for SUM as we haven't your data but it is clear your COUNT expressions are wrong and you'll get the same value (the count of all rows like COUNT(*)).
You have to remove the ",0" in the COUNT expressions.

Re: Difference between sum(decode) and count [message #674717 is a reply to message #674707] Tue, 12 February 2019 07:41
 joy_division Messages: 4944Registered: 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: 1839Registered: 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;```
 Previous Topic: How to collate rows Next Topic: select Statement with CASE and month count
Goto Forum:

Current Time: Wed Mar 20 10:35:41 CDT 2019