This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Simple SQL question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Simple SQL question" Watch "Simple SQL question" New topic
Author

Simple SQL question

Tom Rodrigo
Greenhorn

Joined: Dec 07, 2001
Posts: 26
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

Joined: Jun 07, 2002
Posts: 64
I don't think you could do that, if anyone can, pls let me know.
James Carman
Ranch Hand

Joined: Feb 20, 2001
Posts: 580
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.


James Carman, President<br />Carman Consulting, Inc.
Jaunty John
Greenhorn

Joined: Jul 14, 2002
Posts: 21
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


(((o o)))<br />""--^--"" Entropy is Increasing...
Tom Rodrigo
Greenhorn

Joined: Dec 07, 2001
Posts: 26
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Simple SQL question
 
Similar Threads
compatibility of date format
Joins in Hibernate
JPA: can you use Select statement to create a column
Getting information into a JTable from a drawing Canvas
SubQuery in Hibernate