Oracle Sum data to get 1 per location

Hello Experts Exchange
I have a Oracle table with data in it that I need to sum all the data into one line. I have attached I spreadsheet that has data in it.

Here is the query I have so far;

SELECT
      Sale_Date
    , LOC_Ref
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Womenswear' THEN Price_Paid END),0) AS SI_INCOME1 
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Menswear' THEN Price_Paid END),0) AS SI_INCOME2 
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Childrenswear' THEN Price_Paid END),0) AS SI_INCOME3 
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Footwear/Accessories' THEN Price_Paid END),0) AS SI_INCOME4
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Jackets and Coats' THEN Price_Paid END),0) AS SI_INCOME5
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Books' THEN Price_Paid END),0) AS SI_INCOME6
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Records/Toys/Games' THEN Price_Paid END),0) AS SI_INCOME7
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Bric a Brac' THEN Price_Paid END),0) AS SI_INCOME8
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Linens' THEN Price_Paid END),0) AS SI_INCOME9
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Furniture/Electrical' THEN Price_Paid END),0) AS SI_INCOME10
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Special Events' THEN Price_Paid END),0) AS SI_INCOME11
    , NVL(SUM(CASE WHEN Cat1='New Goods' THEN Price_Paid END),0) AS SI_INCOME12
FROM SITE_INCOME_STAGING
GROUP BY
      Sale_Date, LOC_Ref,Cat1

Open in new window


The data is coming back on multiple lines, please see SQL Results in spreadsheet.

What SQL do I need to have the data in one line?

Regards

SQLSearcher
Oracle-data.xls
SQLSearcherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arun MuruganCommented:
Try this.
SELECT
      Sale_Date
    , LOC_Ref
    , Cat1
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Womenswear' THEN Price_Paid END),0) AS SI_INCOME1 
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Menswear' THEN Price_Paid END),0) AS SI_INCOME2 
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Childrenswear' THEN Price_Paid END),0) AS SI_INCOME3 
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Footwear/Accessories' THEN Price_Paid END),0) AS SI_INCOME4
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Jackets and Coats' THEN Price_Paid END),0) AS SI_INCOME5
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Books' THEN Price_Paid END),0) AS SI_INCOME6
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Records/Toys/Games' THEN Price_Paid END),0) AS SI_INCOME7
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Bric a Brac' THEN Price_Paid END),0) AS SI_INCOME8
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Linens' THEN Price_Paid END),0) AS SI_INCOME9
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Furniture/Electrical' THEN Price_Paid END),0) AS SI_INCOME10
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Special Events' THEN Price_Paid END),0) AS SI_INCOME11
    , NVL(SUM(CASE WHEN Cat1='New Goods' THEN Price_Paid END),0) AS SI_INCOME12
FROM SITE_INCOME_STAGING
GROUP BY
      Sale_Date, LOC_Ref,Cat1

Open in new window

sdstuberCommented:
SQL Searcher -  what do you want the final results to look like?

Arun Murugan - other than adding "Cat1" to the selected results , what is your suggestion supposed to be trying to do?
slightwv (䄆 Netminder) Commented:
I would also like to see expected results.

As a guess until then, try removing cat1 from the group by:
GROUP BY
      Sale_Date, LOC_Ref
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

awking00Information Technology SpecialistCommented:
select sale_date, loc_ref,
sum(decode(cat2,'Womenswear',price_paid)) si_income1,
sum(decode(cat2,'Menswear',price_paid)) si_income2,
sum(decode(cat2,'Childrenswear',price_paid)) si_income3,
sum(decode(cat2,'Footwear/Accessories',price_paid)) si_income4,
sum(decode(cat2,'Jackets and Coats',price_paid)) si_income5,
sum(decode(cat2,'Books',price_paid)) si_income6,
sum(decode(cat2,'Records/Toys/Games',price_paid)) si_income7,
sum(decode(cat2,'Bric a Brac',price_paid)) si_income8,
sum(decode(cat2,'Linens',price_paid)) si_income9,
sum(decode(cat2,'Furniture/Electrical',price_paid)) si_income10,
sum(decode(cat2,'Special Events',price_paid)) si_income11,
sum(decode(cat1,'New Goods',price_paid)) si_income12
from site_income_staging
group by sale_date, loc_ref;
slightwv (䄆 Netminder) Commented:
awking00,

I don't think that is good enough based on the posted SQL.

My guess is 'Womenswear' can exist in more than one cat1.
SQLSearcherAuthor Commented:
Hello sdstuber
I want the results to be the same as the data in the SQL Results spreadsheet, but I only want 1 record as the sale_date and the loc_ref are the same.

Regards

SQLSearcher
slightwv (䄆 Netminder) Commented:
>>but I only want 1 record as the sale_date and the loc_ref are the same.

As I posted above:  try removing cat1 from the group by.

Since you want one row for those two columns, you should group by those two columns.
awking00Information Technology SpecialistCommented:
slightwv,
I thought of that as well, but it did fit the scenario as presented. The other method I thought of was to do something similar using something like select ... where cat1 = something union select ... where cat1 = somethingelse, etc. since neither cat1 or cat2 are required in the ultimate select.
slightwv (䄆 Netminder) Commented:
Unless I'm missing something, I would go with the CASE statement as provided.  Clean and easy.
awking00Information Technology SpecialistCommented:
>>I want the results to be the same as the data in the SQL Results spreadsheet, but I only want 1 record as the sale_date and the loc_ref are the same.<<
That's precisely what my query does. Sorry that I can't seem to get everything to line up, but the values produced below are
for for si_income1,4,5,6,7,8, and 12 with si_income2,3,9,10, and 11 being null (easily converted to zero if needed).

SALE_DATE LOC_REF SI_INC1 SI_INC2 SI_INC3 SI_INC4 SI_INC5 SI_INC6 SI_INC7 SI_INC8 SI_INC9 SI_INC10 SI_INC11 SI_INC12
--------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- -------- -------- --------
23-OCT-15       1   52.82                   13.44   24.46    1.98    1.98   16.93                              44.89

Open in new window

slightwv (䄆 Netminder) Commented:
>>That's precisely what my query does

My guess is so will the original once you remove the extra column in the group by.  This way you don't have to alter a lot of the SQL they already have.

>> Sorry that I can't seem to get everything to line up

Code boxes are fixed based fonts.  I've edited it for you.  If you don't like it, I'll change it back.
awking00Information Technology SpecialistCommented:
>>Unless I'm missing something, I would go with the CASE statement as provided<<
What's missing is that the CASE statement provided doesn't return 1 row (per sale_date and loc_ref).
slightwv (䄆 Netminder) Commented:
>>What's missing is that the CASE statement

I don't believe it is the CASE statement causing that.  I think it is the addition of cat1 to the group by.
awking00Information Technology SpecialistCommented:
Can't see the forest for the trees. I misread the original query and I agree that if the cat1 is removed from the select and the group by, it should produce the results SQL Searcher is looking for. :-)
SQLSearcherAuthor Commented:
Hello Experts Exchange
I did have to remove the cat1 from the group by, but I also had to add the TO_Date in front of the sale_Date field.

The query I ended up with was;

SELECT
      to_date(Sale_Date,'DD-MON-YYYY')
    , LOC_Ref
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Womenswear' THEN Price_Paid END),0) AS SI_INCOME1 
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Menswear' THEN Price_Paid END),0) AS SI_INCOME2 
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Childrenswear' THEN Price_Paid END),0) AS SI_INCOME3 
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Footwear/Accessories' THEN Price_Paid END),0) AS SI_INCOME4
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Jackets and Coats' THEN Price_Paid END),0) AS SI_INCOME5
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Books' THEN Price_Paid END),0) AS SI_INCOME6
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Records/Toys/Games' THEN Price_Paid END),0) AS SI_INCOME7
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Bric a Brac' THEN Price_Paid END),0) AS SI_INCOME8
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Linens' THEN Price_Paid END),0) AS SI_INCOME9
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Furniture/Electrical' THEN Price_Paid END),0) AS SI_INCOME10
    , NVL(SUM(CASE WHEN Cat1='Donated Goods' and Cat2='Special Events' THEN Price_Paid END),0) AS SI_INCOME11
    , NVL(SUM(CASE WHEN Cat1='New Goods' THEN Price_Paid END),0) AS SI_INCOME12
FROM SITE_INCOME_STAGING
GROUP BY
      to_date(Sale_Date,'DD-MON-YYYY'), LOC_Ref
order by to_date(Sale_Date,'DD-MON-YYYY'),LOC_Ref

Open in new window


Regards

SQLSearcher

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
>>but I also had to add the TO_Date in front of the sale_Date field.

Yes, if the date field has a time portion.  To be fair, the sample data you provided didn't...
Mark GeerlingsDatabase AdministratorCommented:
I would never recommend using "to_date" on a column (or variable) that is already a "date" datatype.  If you want to exclude the time portion, simply use "trunc(Sale_Date)".  There is no need to include a format mask then.  Be careful though with using either "trunc" or "to_date" in query "where" clauses.  You can use these operators on bind variables in those clauses with very little penalty, but if you apply them to database columns, the performance penalty can be HUGE!
slightwv (䄆 Netminder) Commented:
>>I would never recommend using "to_date" on a column (or variable) that is already a "date" datatype.

I completely agree and I missed that.  I read it as to_char not to_date.

If the field is a date, I wouldn't suggest to_date on it.
SQLSearcherAuthor Commented:
Found part of the solution by myself.  Thank you to all that tried to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.