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

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

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
--

Uzytkownik "David Portas" wrote:
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