471,092 Members | 1,561 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

SQL problem

Hello everyone

I have a question, something that's been bugging me for some time now.

Let's look at a query

SELECT
A1,
(SELECT SUM(EventValue) FROM Events WHERE Person = A1 AND EventType = 1)
AS EVENT1,
(SELECT SUM(EventValue) FROM Events WHERE Person = A1 AND EventType = 2)
AS EVENT2
FROM
People

will return a recordset with some people and the detailed info that I want
(in this case SUM(of different events))

What if I wanted to get a sum of EVENT1 AND EVENT2
Do I really have to type all that again as in:

((SELECT SUM(EventValue) FROM Events WHERE Person = A1 AND EventType = 1)
+,
(SELECT SUM(EventValue) FROM Events WHERE Person = A1 AND EventType = 2))
AS SUM_EVENTS

If I add EVENT1 + EVENT2 - it obviously won't work.

Best Regards

Wojtek
Jul 23 '05 #1
3 968

Try this

Select sum(Event1), sum(Event2) from (
SELECT
A1,
(SELECT SUM(EventValue) FROM Events WHERE Person = A1 AND EventType =
1)
AS EVENT1,
(SELECT SUM(EventValue) FROM Events WHERE Person = A1 AND EventType =
2)
AS EVENT2
FROM
People
) temp

Madhivanan

Jul 23 '05 #2
Subqueries aren't the best way to get this result anyway. You can use
CASE expressions. The following example also demonstrates the answer to
your question. Reference the calculated columns in a derived table.

SELECT a1, event1, event2,
event1 + event2 AS sum_events
FROM
(SELECT P.a1,
COALESCE(SUM(CASE WHEN eventtype = 1 THEN eventvalue END),0),
COALESCE(SUM(CASE WHEN eventtype = 2 THEN eventvalue END),0)
FROM People AS P
LEFT JOIN Events AS E
ON P.a1 = E.person
GROUP BY P.a1) T(a1, event1, event2)

--
David Portas
SQL Server MVP
--

Jul 23 '05 #3
Uzytkownik "David Portas" wrote:
Subqueries aren't the best way to get this result anyway. You can use
CASE expressions. The following example also demonstrates the answer to
your question. Reference the calculated columns in a derived table.


Thanks a lot David. That helped a lot. There is a huge difference in
performance.
The same query written my way took about 40 seconds to execute - yours 9 :)
Once again Thanks!

Wojtek

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Kostatus | last post: by
117 posts views Thread by Peter Olcott | last post: by
18 posts views Thread by Ian Stanley | last post: by
28 posts views Thread by Jon Davis | last post: by
6 posts views Thread by Ammar | last post: by
2 posts views Thread by Mike Collins | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.