Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Simple SQL question

 
Tom Rodrigo
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Allen Chan
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't think you could do that, if anyone can, pls let me know.
 
James Carman
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can do it with nested queries (I think). Off the top of my head...
select (select sum(blah) blah) as amount_1, (select sum(blah) blah) as amount_2, (select sum(blah) blah) as amount_3 blah blah blah.
 
Jaunty John
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not sure if this is what you had in mind, but here goes:
<CODE>
//I think this would conceviably return three rows,
// accountNo, custName, amount,
//one row of the total of the rows that met each of the three SELECTS.
SELECT ta.accountNo,
ta.customerName,
ta.sum(amount)
FROM tbl_accounts ta
WHERE (ta.accountNo like '22%' AND ta.customerName like 'A%')
UNION
SELECT ta.accountNo,
ta.customerName,
ta.sum(amount)
FROM tbl_accounts ta
WHERE (ta.accountNo like '37%' AND ta.customerName like 'B%')
UNION
SELECT ta.accountNo,
ta.customerName,
ta.sum(amount)
FROM tbl_accounts ta
WHERE (ta.accountNo like '44%' AND ta.customerName like 'C%');
</CODE>
The UNIONs actually run seperate SELECTS.
Hope this helps
 
Tom Rodrigo
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello all,
thanks for all of your replies!
I think I agree with Allen: I cannot do what I wanted.
James: I have looked up those nested query things for quite a couple of times but I don't think this will work.
Bald: I have tried your statement, but didn't work. Maybe my database-system does not support the UNION-expression. But anyway, the result would be not what I needed (only the three amount values by only one query).
Nethertheless thank you very much for your help!
Regards,
Tom
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic