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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor 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.
0
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
All Courses

From novice to tech pro — start learning today.