We help IT Professionals succeed at work.

PLSQL : SQL statement to use decode with multiple expressions

nj07869
nj07869 asked
on
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.
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
what about this:
select name, type, sum(cost) sum_cost
from yourtable
group by name, type

Open in new window

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Author

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.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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?

Author

Commented:
Yes, I always have fixed number of dates. Its for oracle 9i
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
What are your inputs and expected outputs?

Author

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.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.