Special SUM function

[Feanaro]

Hi there,

I've got a peculiar problem with the SUM function.
I have the following data:
[ul]
[li]a list of surfaces[/li]
[li]a status for each surface[/li]
[li]a size for each surface[/li]
[/ul]

I know that there are three possible stati for each surface.

No I would like to have a report that contains the following information:

"Surfacetitle" "Status1 Sum" "Status2 Sum" "Status3 Sum" "Total Sum"

For example:

Production_234    45m²        5m²        12m²        62m²
Acquisition_12    12m²        0         33m²        45m²

That means the Status1 Sum column should contain the total sum of surfaces with the specified title having status 1 and so on. The last column contains the total sum of all stati.

Up to now I have the following SELECT statement:

SELECT Surfaces.surface, SUM(Surfaces.size)
FROM Surfaces, Stati
WHERE Surfaces.Status = Stati.Status
GROUP BY  Surfaces.surface

That's the first and the last column. But what kind of function do I use for the middle columns?
NOTE: Because of some conditions at work I cannot use subqueries (would be rather easy then). It has to be included in this one SELECT statement.

thx alot for your help

Feanaro

 

[rcurva]

How about using DECODE with SUM, like this one;

SELECT Surfaces.surface,
SUM(DECODE(Stati.Status,1,Surfaces.size,0)) "Status1 Sum" ,
SUM(DECODE(Stati.Status,2,Surfaces.size,0)) "Status2 Sum" ,
SUM(DECODE(Stati.Status,3,Surfaces.size,0)) "Status3 Sum" ,
SUM(Surfaces.size) "Total Sum"
FROM Surfaces, Stati
WHERE Surfaces.Status = Stati.Status
GROUP BY Surfaces.surface;

Assuming the valid values for status are 1, 2, & 3.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[blom0344]

Hello Feonaro,

Probably something with a pair of decode's:

SELECT Surfaces.surface AS Surfacetitle,
MAX(DECODE(Status,'1',size,0)) AS Status1_Sum,
MAX(DECODE(Status,'2',size,0)) AS Status2_Sum,
MAX(DECODE(Status,'3',size,0)) AS Status3_Sum,
SUM(Surfaces.size) AS Total_sum
FROM Surfaces
GROUP BY Surfaces.surface

assuming here that Status is either 1,2,3 and that it is a string. I do not know why you used the join with the statustable in the first place. Looks redundant...
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[blom0344]

Hello Robbie,

I think we are on the same track, never seen two reply's that are so near identical (really hadn't seen your reply before mine ......) T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Feanaro]

Thx alot! I'm quite new to Oracle and I would have never had the idea with DECODE.

Does perhaps anyone know some link where one can find a reference of such more uncommon problems with examples?

 

[blom0344]

Hello Feanaro,

For instance:

http://www.orafaq.org/faq.htm

which is really a portal to all kinds of info concerning ORACLE.... T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com