By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

problem in query

P: 25
Hi,
I'm binding a sqldtatasource to a gridview to obtain some calculations from the data. However something is wrong for the query because I know the calculations are wrong. I hope I can explain clearly:

I have data in database as follows:

I have a table with time slots (Slots):
SlotID Slot
=================
1 9-9.30
2 9.30-10
3 10-10.30
4 10.30-11

I have a table with events (Events):
ID Name SlotID ExtraSlotID Limit1 Limit2
===========================================
1 Event1 1 n/a 20 20
2 Event2 1 n/a 20 20
3 Event3 2 n/a 40 40
4 Event4 2 3 20 20
5 Event5 3 4 40 40
6 Event6 3 4 20 20
7 Event7 3 4 10 10

I'm querying my database to get the total of people attending events in each time slot:
SELECT s.Slot, s.SlotID, ISNULL(SUM(e.Limit1), 0) AS PeopleLimit1, ISNULL(SUM(x.Limit2), 0) AS PeopleLimit2
FROM Slots AS s LEFT OUTER JOIN
Events AS e ON e.Slot = s.SlotID LEFT OUTER JOIN
Events AS x ON x.ExtraSlotID = s.SlotID
GROUP BY s.Slot, s.SlotID

However, this is giving me wrong results (I need this table to give me sum of attendees for each slot as Main slot (PeopleLimit1) and as extra slot (PeopleLimit2) but it seems PeopleLimit2 is in a kind of loop and and sums twice when there are events in that slot but as main slot
SlotID PeopleLimit1 PeopleLimit2
================================
1 40 0
2 60 0
3 70 60 *wrong
4 0 70

If I use just main slot with this query:
SELECT s.Slot, s.SlotID, ISNULL(SUM(e.Limit1), 0) AS PeopleLimit1
FROM Slots AS s LEFT OUTER JOIN
Events AS e ON e.Slot = s.SlotID
GROUP BY s.Slot, s.SlotID

I get:
SlotID PeopleLimit1
====================
1 40
2 60
3 70
4 0


If I use only the extra slot in my query:
SELECT s.Slot, s.SlotID, ISNULL(SUM(x.Limit2), 0) AS PeopleLimit2
FROM Slots AS s LEFT OUTER JOIN
Events AS x ON x.ExtraSlotID = s.SlotID
GROUP BY s.Slot, s.SlotID

I get this:
SlotID PeopleLimit2
1 0
2 0
3 20 *correct
4 70

The problem is how to put the last two tables together and sum PeopleLimit1 and PeopleLimit2.

Thanks for your help!
Nov 2 '09 #1

✓ answered by Delerna

I am sure you can get the first query working but you show two other queries
that are correct results and ask
how to put the last two tables together
so I thought I would show you that.
The answer is,
wrap the two queries as derived tables (subqueries)
and query from them


Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT a.fieldsyouneed,b.fieldsyouneed
  3. FROM
  4. (   Your first query goes inbetween these brackets
  5. ) a
  6. appropiate join
  7. (   Your second query goes inbetween these brackets
  8. ) b
  9.  
I will let you attempt to build this
Post back if you have problems

Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
I am sure you can get the first query working but you show two other queries
that are correct results and ask
how to put the last two tables together
so I thought I would show you that.
The answer is,
wrap the two queries as derived tables (subqueries)
and query from them


Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT a.fieldsyouneed,b.fieldsyouneed
  3. FROM
  4. (   Your first query goes inbetween these brackets
  5. ) a
  6. appropiate join
  7. (   Your second query goes inbetween these brackets
  8. ) b
  9.  
I will let you attempt to build this
Post back if you have problems
Nov 2 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.