Home » SQL & PL/SQL » SQL & PL/SQL » How to collate rows (PL/SQL)
How to collate rows [message #674708] Tue, 12 February 2019 03:06 Go to next message
klaartje
Messages: 3
Registered: February 2019
Junior Member
In my query, I am showing the reference of an item and the length of an item.
I want to avoid that I have one row for one length. Instead, I want one row per item, including the different lengths. I am also showing the amount of pieces sold for this item. In this one row, I want the sum of all pieces of all lengths for this item. How can I do this?
Re: How to collate rows [message #674711 is a reply to message #674708] Tue, 12 February 2019 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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: How to collate rows [message #674712 is a reply to message #674708] Tue, 12 February 2019 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your question is not clear, with an example (see previous post) you'll know what you want for sure.

Re: How to collate rows [message #674713 is a reply to message #674712] Tue, 12 February 2019 04:46 Go to previous messageGo to next message
klaartje
Messages: 3
Registered: February 2019
Junior Member
I want one row per DRCENTR.DECARNR. The DESUFCE can be 42, 45, 50, and so on. I don't want 1 row per DESUFCE, but only 1 row per DECARNR. If a DECARNR has for example records with DESUFCE 42 and with DESUFCE 45, I want the data of these records grouped in 1 row. Is this possible?

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)) AS "2016 ",
Count(DECODE(TO_CHAR(DRCENTR.DEDATUT,'YYYY'),'2017',DRCENTR.DECARNR)) AS "2017 ",
Count(DECODE(TO_CHAR(DRCENTR.DEDATUT,'YYYY'),'2018',DRCENTR.DECARNR)) 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: How to collate rows [message #674714 is a reply to message #674713] Tue, 12 February 2019 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Tue, 12 February 2019 11:21

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.

[Updated on: Tue, 12 February 2019 05:41]

Report message to a moderator

Re: How to collate rows [message #674715 is a reply to message #674708] Tue, 12 February 2019 07:00 Go to previous messageGo to next message
EdStevens
Messages: 1082
Registered: September 2013
Senior Member
klaartje wrote on Tue, 12 February 2019 03:06
In my query, I am showing the reference of an item and the length of an item.
I want to avoid that I have one row for one length. Instead, I want one row per item, including the different lengths. I am also showing the amount of pieces sold for this item. In this one row, I want the sum of all pieces of all lengths for this item. How can I do this?
Can you write a query against tables for which you do not have the table structure - the names, data types, and meanings of the columns?

No?

Well, neither can anyone else.
Re: How to collate rows [message #674716 is a reply to message #674715] Tue, 12 February 2019 07:19 Go to previous message
Bill B
Messages: 1870
Registered: December 2004
Senior Member
The bottom line is almost anything is possible. You asked for

I want one row per DRCENTR.DECARNR. The DESUFCE can be 42, 45, 50, and so on. I don't want 1 row per DESUFCE, but only 1 row per DECARNR. If a DECARNR has for example records with DESUFCE 42 and with DESUFCE 45, I want the data of these records grouped in 1 row. Is this possible?

When you say you want them grouped in 1 row, what do you mean? Do you want a value total, do you want a sorted list of "42,45"? You have to show examples of what you want the output to look like. Also the way to do what you want depends on the version of your database. Run the following command against your database and past the answer

select * from v$version;
Previous Topic: Tree View, Connect by Prior on 100m records
Next Topic: Difference between sum(decode) and count
Goto Forum:
  


Current Time: Sun Jul 21 01:52:20 CDT 2019