Accumulation with multiple conditions
-
I'm trying to sum up a column twice, each with a separate condition For example, if we have the following table: Visitors --------
MeetingID LocationID NumVisitors
1 1 3
1 1 5
1 3 7
2 1 9
2 5 3
2 5 4
4 3 10I want to specify 2 Meeting IDs (lets say 1 & 2) and have the query return the following:
LocationID TotalNumVisitors1 TotalNumVisitors2
1 8 9
3 7 0
5 0 7I tried using a full outer join on the table with itself, but that accumulates everything (both ids) together.
Select MeetingID, Sum(Visitors.NumVisitors) as NumVisitors1, Sum(Visitors1.NumVisitors) NumVisitors1
from Visitors
full outer join ...where Visitors.MeetingId = 1 or Visitors1.MeetingId = 2
Group By ...I then tried using sub-queries - that results in dis-joined return set - half of the columns are populated, the other half are null, then it switches
Select Visitors.MeetingId, TotalNumVisitors = (select Sum(Visitors.NumVisitors) from Visitors where Visitors.MeetingId = 1),
TotalNumVisitors1 = (select Sum(Visitors.NumVisitors) from Visitors where Visitors.MeetingId = 2)
where ...LocationID TotalNumVisitors1 TotalNumVisitors2
1 8 NULL
1 NULL 9
3 7 NULL
5 NULL 7Any ideas?
-
I'm trying to sum up a column twice, each with a separate condition For example, if we have the following table: Visitors --------
MeetingID LocationID NumVisitors
1 1 3
1 1 5
1 3 7
2 1 9
2 5 3
2 5 4
4 3 10I want to specify 2 Meeting IDs (lets say 1 & 2) and have the query return the following:
LocationID TotalNumVisitors1 TotalNumVisitors2
1 8 9
3 7 0
5 0 7I tried using a full outer join on the table with itself, but that accumulates everything (both ids) together.
Select MeetingID, Sum(Visitors.NumVisitors) as NumVisitors1, Sum(Visitors1.NumVisitors) NumVisitors1
from Visitors
full outer join ...where Visitors.MeetingId = 1 or Visitors1.MeetingId = 2
Group By ...I then tried using sub-queries - that results in dis-joined return set - half of the columns are populated, the other half are null, then it switches
Select Visitors.MeetingId, TotalNumVisitors = (select Sum(Visitors.NumVisitors) from Visitors where Visitors.MeetingId = 1),
TotalNumVisitors1 = (select Sum(Visitors.NumVisitors) from Visitors where Visitors.MeetingId = 2)
where ...LocationID TotalNumVisitors1 TotalNumVisitors2
1 8 NULL
1 NULL 9
3 7 NULL
5 NULL 7Any ideas?
What database are you using? Some have
Pivotbuiltin. Otherwise you can do it as a variation of this query:Select LocationID
,Sum(Case when MeetingID = 1 then Numvisitors else 0) as TotalNumVisitors1
,Sum(Case when MeetingID = 2 then Numvisitors else 0) as TotalNumVisitors2
From Visitors
Group By LocationIDLight moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
What database are you using? Some have
Pivotbuiltin. Otherwise you can do it as a variation of this query:Select LocationID
,Sum(Case when MeetingID = 1 then Numvisitors else 0) as TotalNumVisitors1
,Sum(Case when MeetingID = 2 then Numvisitors else 0) as TotalNumVisitors2
From Visitors
Group By LocationIDLight moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
It's actually a query used to populate an rdl report. Would that work if the calculation is more complex? It's actually adding the rows together, then multiplying by a number from a different table. Something like this maybe:
Select LocationID, Groups.GroupSize
,Sum(Case when MeetingID = 1 then (Group)*GroupSize + Numvisitors else 0) as TotalNumVisitors1
,Sum(Case when MeetingID = 2 then (Group)*GroupSize + Numvisitors else 0) as TotalNumVisitors2
From Visitors
join Groups on Visitors.SomeID = Groups.SomeID
Group By LocationID, GroupSizeI could probably work around this by adding some logic in the report, but it seems a bit cleaner to do my data handling in the query and have the report only display the data. And I figured there got to be an easy way to do that :rolleyes: I'll give it a try later, but it should probably work right?
-
It's actually a query used to populate an rdl report. Would that work if the calculation is more complex? It's actually adding the rows together, then multiplying by a number from a different table. Something like this maybe:
Select LocationID, Groups.GroupSize
,Sum(Case when MeetingID = 1 then (Group)*GroupSize + Numvisitors else 0) as TotalNumVisitors1
,Sum(Case when MeetingID = 2 then (Group)*GroupSize + Numvisitors else 0) as TotalNumVisitors2
From Visitors
join Groups on Visitors.SomeID = Groups.SomeID
Group By LocationID, GroupSizeI could probably work around this by adding some logic in the report, but it seems a bit cleaner to do my data handling in the query and have the report only display the data. And I figured there got to be an easy way to do that :rolleyes: I'll give it a try later, but it should probably work right?
Probably. But personally I'm reluctant to use SQL for presentation logics, you'll find that it isn't very flexible. I would simply get the data you need:
Select LocationID
,MeetingID
,Sum(Numvisitors) as TotalNumVisitors
From Visitors
where MeetingID IN (1,2)
Group By LocationID,MeetingIDAnd then do the Pivot in Reporting Services where it's called "Matrix control". (Here I assume that your "rdl report" means you're using SSRS)
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions