Re: Query Help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 12 May 1998 16:20:11 GMT
Message-ID: <355b75f1.11407162@192.86.155.100>

A copy of this was sent to "Scott A. Schell" <saschell_at_lucent.com> (if that email address didn't require changing) On Tue, 12 May 1998 15:14:49 GMT, you wrote:

>Hi All,
>
> My Table looks like:
>
> month, department, csub, project, amount
>
>ex
>
> jan, dept1, a, proj1, 1.1
> jan, dept1, b, proj1, 2.2
> jan, dept1, c, proj1, 3.3
> ...
>
> where csub is one of 4 values (a, b, c, d -- for example).
>
> I need to create a query which results in the following layout:
>
>deptartment, (a), (b), (c), (d)
>
> Where the query returns all departments (by month/project) and
>for each department returns the amounts for csub a, b, c, d in it's
>own row for example:
>
>dept1, 1.1, 2.2, 3.3, 4.4
>dept2, 5.5, 6.6, 7.7, 8.8
>....
>

select department, month, project 
       sum( decode( csub, 'a', amount, 0 ) ) '(a)',
       sum( decode( csub, 'b', amount, 0 ) ) '(b)',
       sum( decode( csub, 'c', amount, 0 ) ) '(c)',
       sum( decode( csub, 'd', amount, 0 ) ) '(d)'

  from T
 where month = 'JAN'
 group by department, month, project
/  

>(again for a given project/month -- i.e. proj1, JAN...)
>
>Please respond both by email and news.
>
>Thanks
>
>Scott
>saschell_at_lucent.com
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue May 12 1998 - 11:20:11 CDT