Hello all,
I'm wondering if it is possible to get the following three sql-queries in one:
Select sum(amount) as amount1 from tbl_accounts where accountNo like '22%' and customerName like 'A%';
Select sum(amount) as amount2 from tbl_accounts where accountNo like '37%' and customerName like 'B%';
Select sum(amount) as amount3 from tbl_accounts where accountNo like '44%' and customerName like 'C%';
... so that I will get amount1, amount2, amount3 without querying the database three times;
[Note that accountNo and/or customerName can be the same for each query]
I know I can do
Select accountNo, customerName, sum(amount) from tbl_accounts
where
((accountNo like '22%' and customerName like 'A%') or
(accountNo like '37%' and customerName like 'B%') or
(accountNo like '44%' and customerName like 'C%'))
group by accountNo, customerName;
But this is not the same, because I would then get:
22345, avalon inc., 300000
22567, avalon inc., 100000
22664, another inc., 2000
22778, alpha-beta, 234990
37143, betaGames, 10000
37232, big sports, 200000
37734, barcks sparks, 19
44322, cesars palace, 12234
44449, choco dreams, 934290
44450, choco dreams, 60000
44589, cold airwaves, 50000
(so I will have to sum up the amounts for each accountNo starting with 22 (or 37 or 44) )
Is there a way to achieve this (getting the result like amount1, amount2, amount3)?
Thanks,
Tom
[ August 29, 2002: Message edited by: Tom Rodrigo ]