Question about a SQL query 2

[mmiram]

Hi all:

I am new to SQL server and am running into a few issues with my queries. I have the following query:

SELECT SubdivisionName, sum(DivCount)
FROM

(SELECT Subdivisions.SubdivisionName, HouseDetails.HouseCount AS DivCount, Subdivisions.SubdivisionID
FROM Subdivisions, HouseDetails WHERE Subdivisions.SubdivisionID = HouseDetails.SubdivisionID
AND (Subdivisions.SubdivisionID IN (2,3))AND (HouseDetails.DwellingID IN (1,2,3)))

Group By SubdivisionName

I am basically trying to summarize the results that I receive in the sub query by using the "Sum" function and the "group by" clause. I don't want the dwellingID to be taken into account while summarizing the date.

The sub query when I run it by itself runs fine. However, as a whole, the query does not run. It generates the following error.


Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Group'.


Can you tell me what is wrong?

Thanks.

 

[SQLDenis]

sure

SELECT SubdivisionName, sum(DivCount)
FROM 

(SELECT Subdivisions.SubdivisionName, HouseDetails.HouseCount AS DivCount, Subdivisions.SubdivisionID
FROM Subdivisions, HouseDetails WHERE Subdivisions.SubdivisionID = HouseDetails.SubdivisionID 
AND (Subdivisions.SubdivisionID IN (2,3))AND (HouseDetails.DwellingID IN (1,2,3))) [red]x[/red]

Group By SubdivisionName

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Subqueris must ahve an alias.

SELECT SubdivisionName, sum(DivCount)
FROM 

(SELECT Subdivisions.SubdivisionName, HouseDetails.HouseCount AS DivCount, Subdivisions.SubdivisionID
FROM Subdivisions, HouseDetails WHERE Subdivisions.SubdivisionID = HouseDetails.SubdivisionID 
AND (Subdivisions.SubdivisionID IN (2,3))AND (HouseDetails.DwellingID IN (1,2,3)))[!] As X[/!]

Group By SubdivisionName

In this case, you can alias it with anything you want.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oh, sure. Denis's will also work.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Unoriginal, couldn't you choose y? ;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mmiram]

It works great Thanks a lot.