Condtional SUM() of fields

[AP81]

Given the sample 'invoices' table (bottom), I am trying to do a single query which will return the following:

SELECT (SUM(AMOUNT) WHERE TYPE = 's') AS S, (SUM(AMOUNT) WHERE TYPE = 'm') as M, SUM(AMOUNT) as TOTAL 
FROM invoices
WHERE CUST_CODE = 'ALEX01'

+-----------+--------+----------------+
| CUST_CODE |   S    |   P    | TOTAL |
+-----------+--------+----------------+
| ALEX01    |607.28  | 142.72 |750    |
+-------------------------------------+

Is this possible? Maybe using an IF conditional???
I know that it is a lot easier to do separate queries for each SUM(), but I'd like to do it in one hit.

Thanks in advance.

Sample data from 'invoices' table.
+-----------+--------+---------------+--------+
| CUST_CODE | AMOUNT | ITEM_CODE     | TYPE   |
+-----------+--------+---------------+--------+
| ALEX01    |  68.58 | /LicenceHW    | s      |
| ALEX01    |  68.64 | /LicenceHW    | s      |
| ALEX01    |  44.93 | /HDL-RD       | m      |
| ALEX01    |  44.93 | /HDL-RD       | m      |
| ALEX01    |  44.93 | /HDL-ED       | m      |
| ALEX01    |  44.93 | /HDL-ED       | m      |
| ALEX01    |  44.93 | /HDL-CX       | m      |
| ALEX01    |  44.93 | /HDL-CX       | m      |
| ALEX01    |  44.93 | /HDL-CR       | m      |
| ALEX01    |  44.93 | /HDL-CR       | m      |
| ALEX01    |  89.86 | /HDL          | m      |
| ALEX01    |  89.86 | /HDL          | m      |
| ALEX01    |  34.06 | /HDA-CF       | m      |
| ALEX01    |  34.06 | /HDA-CF       | m      |
| ALEX01    |    5.5 | /Account      | s      |
+-----------+--------+---------------+--------+

------------------------------------
There's no place like 127.0.0.1
------------------------------------

 

[AP81]

I think I worked it out ...

SELECT 
SUM(CASE WHEN TYPE='s' THEN AMOUNT ELSE 0 END) AS S, 
SUM(CASE WHEN TYPE='m' THEN AMOUNT ELSE 0 END) AS M
FROM invoices
WHERE CUST_CODE = 'ALEX01'

This seems to work relatively fast, but if this can be improved, I'd like to know!


------------------------------------
There's no place like 127.0.0.1
------------------------------------

 

[feherke]

Hi

That is how I also solve this kind of problems. I do not think it can be done more efficiently.

The ideal would be to use [tt]group by[/tt], but is useless for example in reporting tools.

[b]select[/b] type,sum(amount)
[b]from[/b] invoices
[b]where[/b] cust_code=[i]'ALEX01'[/i]
[b]group by[/b] type

Feherke.

http://rootshell.be/~feherke/