SQL Question - Group By Issue

Forums › Off topic › SQL Question - Group By Issue

ajobbins

5052 posts

Uber Geek

Trusted

#36178 21-Jun-2009 15:13

I have an SQL query that includes a group by statement as well as a where clause.

The code looks like this:
SELECT one, AVG(two) AS Result
FROM dbo.[db_main]
WHERE (three = 1) AND (four = 'word') AND (five = 1)
GROUP BY one
ORDER BY one

The problem is that for the 'one' column, there are 93 unique records (Exisitng 1020 times each in the table, hence the AVG and GROUP BY part of this query).

The data is such that there is not necessarily a row that meets the criteria of this query for each of the 93 'ones', however I need the result set to show each 'one' with a value, even if there is no value.

Hope that makes sense. Any ideas?

mushion22

297 posts

Ultimate Geek

Trusted

#227156 21-Jun-2009 16:36

select a.one, b.Result from dbo.[db_main] a left join (SELECT one, AVG(two) AS Result

FROM dbo.[db_main]

WHERE (three = 1) AND (four = 'word') AND (five = 1)

GROUP BY one

ORDER BY one) b on a.one = b.one;

dunno if that works in MSSQL, should do in MySQL.

mushion22

297 posts

Ultimate Geek

Trusted

#227158 21-Jun-2009 16:39

not sure if there is a function already in mssql, but if not you could write one to replace a null value with 0 if you needed an actual Result figure for all the records in that join (if a left join, all the records from the left table are shown, and where there is no corressponding record from the right table, it inserts null).

ajobbins

5052 posts

Uber Geek

Trusted

#227162 21-Jun-2009 17:05

Didn't work, but I got there in a round-about way. Thanks tho

jsauni

65 posts

Master Geek

#227684 23-Jun-2009 13:03

mushion22: not sure if there is a function already in mssql, but if not you could write one to replace a null value with 0 if you needed an actual Result figure for all the records in that join (if a left join, all the records from the left table are shown, and where there is no corressponding record from the right table, it inserts null).

@mushion22: isnull function will replace null values with whatever specified.

http://msdn.microsoft.com/en-us/library/ms184325.aspx

@adamj: what was your working query? the original that you posted looks like it should've worked.

ajobbins

5052 posts

Uber Geek

Trusted

#227725 23-Jun-2009 17:12

Didn't get a working query. Just ended up using Excel to get what I wanted