SQL statement with Group by - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL statement with Group by

Author

Topic

Dohmaker
Starting Member

4 Posts

Posted - 2007-10-17 : 11:08:53

hi everyone,

I have a little problem concerning a SQL statement I need to do.

here is my statement:

SELECT     Location, [Week of Availability], GSDC_Name, PA_Name, CA_Name, MAX([Year of Availability]) AS [Year]
FROM         dbo.tbl_availability
GROUP BY Location, GSDC_Name, PA_Name, CA_Name, [Week of Availability]

now the problem in this statement is that I don't want to group by Week of Availability, but I do want to select it. BUT, if I remove Week of availability from the Group by part, I get an error! how can I do this?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-17 : 11:13:56

Which [Week of Availability] do you want with the group?
SELECT Location, [Week of Availability] = max([Week of Availability]), GSDC_Name, PA_Name, CA_Name, MAX([Year of Availability]) AS [Year]
FROM dbo.tbl_availability
GROUP BY Location, GSDC_Name, PA_Name, CA_Name
SELECT Location, [Week of Availability] = min([Week of Availability]), GSDC_Name, PA_Name, CA_Name, MAX([Year of Availability]) AS [Year]
FROM dbo.tbl_availability
GROUP BY Location, GSDC_Name, PA_Name, CA_Name

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Dohmaker
Starting Member

4 Posts

Posted - 2007-10-17 : 11:26:28

that is the problem, I do not want to have either the max or the min week of availability, I just want all the weeks from the latest year. However the way I did the statement, since its grouping by week, I get a week from 2006 since there are no entries in 2007 with the same week number.

Dohmaker
Starting Member

4 Posts

Posted - 2007-10-17 : 14:57:09

...anyone? is what I am trying to do too complicated? I doesn't SEEM that complicated...

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-10-17 : 21:42:57

How do you expect to see all the weeks from the latest year if there are none in 2007? What do you expect to see?

I'm guessing that you want a table with the weeks outer joined to your query on week of availability.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-17 : 21:50:27

How about some sample data and expected results so that we can be sure of exactly what you are looking for. Keep your sample data brief, but be sure to cover all of the possibilities that the SQL code will need to handle.

- Jeff
http://weblogs.sqlteam.com/JeffS

Dohmaker
Starting Member

4 Posts

Posted - 2007-10-19 : 09:44:34

oh its fine, I had a friend help me for this. I'm not sure he showed me the simplest way to do it though... here's what we did:

SELECT     ta1.Location, ta1.GSDC_Name, ta1.PA_Name, ta1.CA_Name, ta1.[Year of Availability] AS [Year], ta1.[Week of Availability]
FROM         dbo.tbl_availability ta1 INNER JOIN
                          (SELECT     Location, GSDC_Name, PA_Name, CA_Name, MAX([Year of Availability]) AS [Year]
                            FROM          dbo.tbl_availability ta2
                            GROUP BY Location, GSDC_Name, PA_Name, CA_Name) tmp ON ta1.Location = tmp.Location AND ta1.GSDC_Name = tmp.GSDC_Name AND 
                      ta1.PA_Name = tmp.PA_Name AND ta1.CA_Name = tmp.CA_Name AND ta1.[Year of Availability] = tmp.[Year]

I still barely understand it though... hehe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources