PLSQL : SQL statement to use decode with multiple expressions

Can any one give some ideas to write a sql statement which has functionality of similar to using decode with multiple expressions.. for eg.

table has name, type, cost

data : name1, type1, 11
          name1, type1, 11
          name1, type2, 12

I need result set of name, type cost  as name1, type1, 22
basically sum of cost.
nj07869Asked:
Who is Participating?
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
select name, type, sum(cost) sum_cost
from yourtable
group by name, type

Open in new window

0
sdstuberCommented:
are you trying ig drop the name1,type2  row out of your summation?
or were you just illustrating one of the sum's?

if the former... here's a slight mod to angeliii's solution.  if the latter, take his as is.


select name,type,sum(cost) sum_cost
from yourtable
where type != 'type2'
group by name,type
0
nj07869Author Commented:
I need decode to turn first grouping of rows into columns. There is also date field in picture! eg.

SELECT name, type,
NVL(sum(DECODE(date,'18-JAN-08',cost)),0) as "18-JAN-08",
NVL(SUM(DECODE(date,'18-JAN-08',cost,0)), 0) as Total
from yourtable where name in ('xxx', 'yyy')

so basically from the query above I have one column for each date (obviously I will add one "NVL(sum(decode" for each date I need in results). NOW I want the date column to breakup into two columns (there are two types type1 and type2).

Please let me know if any questions. Sorry for not being clear before.

Thanks.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sdstuberCommented:
Do you have a known number of dates?  If not, you can't do this.  SQL statements MUST have a fixed number of columns.  You can write dynamic sql, but that does nothing more than generate a statement.  That statement, once generated, has some known number of columns PRIOR to execution.

Rather than pseudo-code, can you show more complete sample input data and what you would want the output to look like?
Also, what version of oracle is this for?

0
nj07869Author Commented:
Yes, I always have fixed number of dates. Its for oracle 9i
0
sdstuberCommented:
What are your inputs and expected outputs?

0
nj07869Author Commented:
for the sql:  SELECT name, type,
NVL(sum(DECODE(date,'18-JAN-08',cost)),0) as "18-JAN-08",
NVL(SUM(DECODE(date,'18-JAN-08',cost,0)), 0) as Total
from yourtable where name in ('xxx', 'yyy')

the result set will be ....
name    type     18-JAN-08   Total
name1  type1   2400          2400
name1  type2   2300          2300
name2  type1   2800          2800
name2  type2   2900          2900

what I need is...

name    18-JAN-08-type1  18-JAN-08-type2  Total
name1  2400                     2300                    4700
name2  2800                     2900                    5700

The Total is not important, i can do it later but just that I need the types in columns.  I can combine date and type (as date + '-' + type) but its giving me 'not number' error.  Do you know why?

I was trying to do... NVL(sum(DECODE(date + '-' 'type1','18-JAN-08-type1',cost)),0)
do you know why the above won't work? If this works, it can be the solution of my issue.

Thanks.
0
sdstuberCommented:
without an example of what you want the output to be I'm "guessing" this is what you might want.


SELECT   NAME,
         NVL(SUM(CASE
                     WHEN DATE = TO_DATE('18-JAN-2008', 'dd-MON-yyyy') AND TYPE = 'type1'
                         THEN COST
                 END),
             0
            ) AS "18-JAN-08 type1",
         NVL(SUM(CASE
                     WHEN DATE = TO_DATE('18-JAN-2008', 'dd-MON-yyyy') AND TYPE = 'type2'
                         THEN COST
                 END),
             0
            ) AS "18-JAN-08 type2",
         NVL(SUM(CASE
                     WHEN DATE = TO_DATE('18-JAN-2008', 'dd-MON-yyyy')
                         THEN COST
                 END), 0) AS "18-JAN-08 Total",
         NVL(SUM(CASE
                     WHEN DATE = TO_DATE('18-JAN-2008', 'dd-MON-yyyy') AND TYPE = 'type1'
                         THEN COST
                 END),
             0
            ) AS "19-JAN-08 type1",
         NVL(SUM(CASE
                     WHEN DATE = TO_DATE('18-JAN-2008', 'dd-MON-yyyy') AND TYPE = 'type2'
                         THEN COST
                 END),
             0
            ) AS "19-JAN-08 type2",
         NVL(SUM(CASE
                     WHEN DATE = TO_DATE('18-JAN-2008', 'dd-MON-yyyy')
                         THEN COST
                 END), 0) AS "19-JAN-08 Total",
         NVL(SUM(CASE
                     WHEN TYPE = 'type1'
                         THEN COST
                 END), 0) AS "type1 Total",
         NVL(SUM(CASE
                     WHEN TYPE = 'type2'
                          THEN COST
                 END), 0) AS "type2 Total",
         NVL(SUM(COST), 0) AS "Total"
    FROM yourtable
   WHERE NAME IN('xxx', 'yyy')
GROUP BY NAME
0
sdstuberCommented:
oh sorry, posting at same time...

Thanks for the eample.  looks like I guessed correctly.


You can't use "+" to combine strings, that's sql server syntax.  Oracle uses "||" to concatenate strings.

But you don't need to combine date and the type into a single value to use decode
Case will let you check two values instead of decode which will only let you check one.

Also,  ALWAYS use to_date with datestrings, don't use strings by themselves without an explicit format,
it's just begging to have an error later on.
0

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
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.