simple SQL (T-SQL) help

[Cerb]

Not complicated, right? Of course not, I just can't figure it out. I'm a SQL n00b.

There's a list of, let's say houses, and each has a type ID. Then, there's a table that has the type ID and type names matched. The results returned should have a single row for each type of house with the ID and then name, and the average square feet of that type. If that type is not in the table with the description, it should be ignored.

SELECT Houses.Type_ID, AVG (SqFt) As avgSqFt FROM Houses,Types GROUP BY Types.Type_ID ORDER BY Types.Type_ID

Seems to work, but just gets back the numbers, not names. What do I need to do to get the names as another column? I've now forgotten details of some of what I tried, but SQL was having nothing to do with any of it 🙂.

<- is cheap, and got the free super saver shipping, so yes, I have books on the way, one of these days 🙂

http://rafb.net/p/DavzV942.html (was attached code)

 

[troytime]

do you want all the houses, or all the types?

is the description in the Houses table?

show the table structures, your query will be a breeze 🙂
i suck at reading comprehension though, sorry 🙁

 

[troytime]

oh, and use JOIN to only get rows that are present in both tables

SELECT t.type_name, AVG(h.SqFt) as avgSqFt, h.otherColNamesHere FROM Houses h
JOIN Types t ON t.Type_ID = h.Type_ID

 

[JACKDRUID]

SELECT house.name, AVG(sqft) as avgsqft FROM house INNER JOIN types ON house.type_id = types.typePid ORDER BY types.type_id ASC

 

[Cerb]

Originally posted by: JACKDRUID
SELECT house.name, AVG(sqft) as avgsqft FROM house INNER JOIN types ON house.type_id = types.typePid ORDER BY types.type_id ASC

So, GROUP BY is no longer needed to get the averages going properly? IIRC, I tried some variants of that, but kept the GROUP BY, and it complained that what Types.Type_Name (house.name in your command, I think) was not in the aggregate function, and then something else. Does the GROUP BY become assumed, or otherwise unneeded from the INNER JOIN?

I could get as far as having two columns, where one was the ID, and the other was the sum; but really want the name in there, and will want to do the same sort of thing for many different queries pretty soon.

is the description in the Houses table?

No. Just two int columns.

show the table structures, your query will be a breeze

Here's a shot at it:

CREATE TABLE Houses (
SqFt int,
Type_ID int )
CREAT TABLE Types (
Type_ID int,
Type_Name varchar (50) )
CREATE TABLE WANT (
Type_ID int,
Type_Name varchar (50),
avgSqFt int ) /* it won't really be an int, but I can handle details like casting */

What I'm trying to replace uses subqueries for each row (IE, "(SELECT DISTINCT TOP 1 XXX ORDER BY...)"), loops, and cursors...and that just rubs me the wrong way, even as a a n00b, and I need to lighten the DB CPU load. The app kind of fell into my lap, but I don't want the next frightened n00b who gets to maintain it to have as much to fix (make no mistake, when it passes on, it will go to another person with no experience at the time something breaks--best way to start learnin'! 🙂).

 

[Furor]

They forgot the group by...try this...

SELECT Types.Type_Name, AVG(Houses.SqFt) As avgSqFt
FROM Houses
INNER JOIN Types on Houses.Type_ID = Types.Type_ID
GROUP BY Types.Type_Name

 

[JACKDRUID]

oops.. forgot the group by .. ya furor is correct. 😀

 

[troytime]

i didn't forget the group by, i just forgot that the OP is new to sql 😛

 

[Cerb]

Originally posted by: Furor
They forgot the group by...try this...

SELECT Types.Type_Name, AVG(Houses.SqFt) As avgSqFt
FROM Houses
INNER JOIN Types on Houses.Type_ID = Types.Type_ID
GROUP BY Types.Type_Name

Close, but how do I get the extra int column in the result?

SELECT Types.Type_ID,Types.Type_Name, AVG(Houses.SqFt) As avgSqFt
FROM Houses
INNER JOIN Types on Houses.Type_ID = Types.Type_ID
GROUP BY Types.Type_Name

...doesn't work (and shouldn't).

 

[KLin]

Originally posted by: Cerb

Originally posted by: Furor
They forgot the group by...try this...

SELECT Types.Type_Name, AVG(Houses.SqFt) As avgSqFt
FROM Houses
INNER JOIN Types on Houses.Type_ID = Types.Type_ID
GROUP BY Types.Type_Name

Close, but how do I get the extra int column in the result?

SELECT Types.Type_ID,Types.Type_Name, AVG(Houses.SqFt) As avgSqFt
FROM Houses
INNER JOIN Types on Houses.Type_ID = Types.Type_ID
GROUP BY Types.Type_Name

...doesn't work (and shouldn't).

Include it in the group by clause.

GROUP BY Types.Type_ID, Types.Type_Name

 

[Cerb]

Ah! So simple...that did it.

...and, SQL for Mere Mortals got here.

Thanks for the help, guys!