Joins and grouping
-
Hi.
I'm not even sure this is the correct sub-topic within this forum to post this question, but I thought I would give it a shot.
I have 3 tables: ttSessions, ssLocation and ssDayofWeek. I'm using these tables to run a SQL Select on, in order to produce a timetable on a website for someone. You can see the results here:
Now, the data is being returned, but I would like to be able to further group the results by location. At the moment, if there is more than once class per location, it lists it as a separate/new entry, as shown on the website (see the Highbury Pool entries for example).
Is there a way to manipulate this data so that the location is shown only once with all subsequent entries associated with it underneath, in SQL?
The SQL I used to get this far is:
SELECT ttLocation.LOCATION, ttDayOfWeek.DAY AS Expr1, ttSessions.START_TIME, ttSessions.END_TIME, ttSessions.LEVEL, ttSessions.COST
FROM ttSessions INNER JOIN
ttLocation ON ttSessions.LOCATION_ID = ttLocation.ID INNER JOIN
ttDayOfWeek ON ttSessions.DAY = ttDayOfWeek.ID
ORDER BY ttSessions.DAY, ttSessions.START_TIME, ttLocation.LOCATION
Any takers please?
Thanks in advance,
David
-
I cant see you image post. But one way to do this is to take the MAX of the class and remove class from the group by. Another way is to simply remove class from the query.
-
You lost me there - MAX?
I've also got to admit I'm not a DBA by any means either!
Could you elaborate on your answer a little please?
SELECT ttLocation.LOCATION, ttDayOfWeek.DAY AS Expr1, ttSessions.START_TIME, ttSessions.END_TIME, ttSessions.LEVEL, ttSessions.COST
FROM ttSessions INNER JOIN
ttLocation ON ttSessions.LOCATION_ID = ttLocation.ID INNER JOIN
ttDayOfWeek ON ttSessions.DAY = ttDayOfWeek.ID
ORDER BY ttSessions.DAY, ttSessions.START_TIME, ttLocation.LOCATION
Thanks,
David
-
Ok. Let me back up then. What I am saying is when you use a group by clause to group a set of aggregate data. Your data may return unexpected results if you have distinct values in any of your group by columns. See below:
DECLARE @T TABLE(
MYCOLUMN1 INT,
MYCOLUMN2 CHAR(1),
MYCOLUMN3 VARCHAR(10)
)
INSERT INTO @T
SELECT 1, 'Y', 'TEST' UNION ALL
SELECT 1, 'Y', 'TEST2' UNION ALL
SELECT 2, 'Y', 'TEST3'
--****THIS WILL PRODUCE THE WRONG RESULTS****
--ID 1 WILL DUPLICATE BECAUSE MYCOLUMN3 HAS 2 DISTINCT VALUES
SELECT MYCOLUMN1, MYCOLUMN2, MYCOLUMN3
FROM @T
GROUP BY MYCOLUMN1, MYCOLUMN2, MYCOLUMN3
--YOU CAN DO THIS TO REMEDY THE QUERY
--MAX STATES THAT YOU WANT THE BIGGEST VALUE FROM COLUMN 3 FOR EACH ID
SELECT MYCOLUMN1, MYCOLUMN2, MAX(MYCOLUMN3)
FROM @T
GROUP BY MYCOLUMN1, MYCOLUMN2
--YOUR ONLY OTHER OPTION IS TO LEAVE IT OUT OF THE QUERY
SELECT MYCOLUMN1, MYCOLUMN2
FROM @T
GROUP BY MYCOLUMN1, MYCOLUMN2
-
Ah, I see now... thanks for your help so far!
I got the SQL looking like this now:
SELECT MAX(ttLocation.LOCATION) AS Expr2, ttSessions.START_TIME, ttSessions.END_TIME, ttSessions.LEVEL, ttSessions.COST, ttDayOfWeek.DAY,
ttSessions.DAY AS Expr1, ttLocation.LOCATION
FROM ttSessions INNER JOIN
ttLocation ON ttSessions.LOCATION_ID = ttLocation.ID INNER JOIN
ttDayOfWeek ON ttSessions.DAY = ttDayOfWeek.ID
GROUP BY ttDayOfWeek.DAY, ttSessions.START_TIME, ttSessions.END_TIME, ttSessions.LEVEL, ttSessions.COST, ttSessions.DAY,
ttLocation.LOCATION
ORDER BY Expr1, ttSessions.START_TIME, ttLocation.LOCATION
The results are still the same though (also tried inserting 'DISTINCT' after SELECT too - same result too.)
I may have to look at this from the VB.NET side and see if I can find some help manipulating this... I guess what I'm looking for is to show 'For each LOCATION, these are the results of the classes,times, etc' - but not to repeat the LOCATION etc - was hoping to possibly get SQL to do this (I could get this done in Reporting Services a while ago) - the SQL is doing it's job though - it's returning all the right values, I just need to look at the presentation of those results.
I'll keep looking, but thanks again for your help so far. 🙂
David
-
David,
It sounds like you want all of the rows, but you only want to display the location for the first location value in each group. Is this correct? If so, this is normally something that you would want to handle at the presentation layer. Reporting services, Crystal reports, or application code are best for this type of action. That does not mean that it cannot be done with T-SQL, it just means that it is better suited for the presentation layer. Here is a link to another thread with (what I think is) a like problem. I gave a solution there thay could be used to help you, but there is more overhead involved with it than a normal SELECT so I'm not sure why you would want to put your DB server through that if you don't have to.
http://www.sqlservercentral.com/Forums/Topic452918-8-1.aspx#bm452983
-
I agree with John. For the specs you are suppling, it sounds like you want to produce a SSRS report. SSRS allows you to group data together without repeating headers and makes SQL output much more legible.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply