Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
neil_akoga
Yak Posting Veteran
56 Posts |
 Posted - 2008-09-15 : 09:11:32
|
| Hi, I'm trying to write a query but i'm a bit unsure how to do it. (it's probably straight forward but any help is much appreciated). Here's some background info:I have two tables tblCourses and tblOrdersexample columns are belowtblCoursescourseIDcourseNameCourseDateetctblOrderscourseIDorderIDdelegateCount(basically the amount of people booked on the course under this orderID)etcwhat i want to do is create a table that shows as followscourse / total bookings / other things like course date,etcso i need to show all courses and add up all the delegate counts on all the Orders that are applicable to the courseIDso if i have 2 entries in tblOrders for course ABC and each one is for 5 delegates i'd expect to get a total bookings for that row of 10 and so on for all the courses available in tblCourses (i'd obviously need to show a 0 if there are no bookings in tblOrders)I presume that this will need to go into a view. The basic premise i presume is to create some kind of nested select statement that does some kind of looping calculation based on my courseID against tblOrders. Anyone know how to do this or can provide a link to any tutorials that can show me how to do this? Thanks in advance. Neil |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-15 : 09:27:13
|
[code]SELECT c.courseName, c.courseDate, SUM(COALESCE(o.delegateCount, 0)) AS delegateCountFROM tblCourses AS cLEFT JOIN tblOrders AS o ON o.courseID = c.courseIDGROUP BY c.courseID, c.courseName, c.courseDate[/code]
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
neil_akoga
Yak Posting Veteran
56 Posts |
Posted - 2008-09-15 : 09:35:34
|
| you my good man. are an absolute genius and a lifesaver. thanks more than i can express in words. neil |
|
|
|
|
|
|
sql query help needed