What's wrong with this having syntax

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

Database Design and Application Architecture

What's wrong with this having syntax

Author

Topic

scope
Starting Member

6 Posts

Posted - 2007-07-28 : 17:05:24

It works in ASP-Access, but not on the Unix sql:

SELECT SUM(result.pt) as SumOfpt FROM result,team GROUP BY sref,tref,team.id HAVING sref=2 and team.id=tref;

it says - and only on the unix server:

SQLState: 4 Native Error Code: 1054 [TCX][MyODBC]Unknown column 'sref' in 'having clause'
/test/teamstandings.asp, line 20

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-28 : 17:07:38

1) HAVING ... team.id = tref is not any aggregate function, which HAVING needs.
2) GROUP BY sref is not needed since you only have sref= 2 records.

SELECT SUM(result.pt) as SumOfpt FROM result inner join team on team.id=tref
where sref = 2
GROUP BY tref, team.id

E 12°55'05.25"
N 56°04'39.16"

scope
Starting Member

6 Posts

Posted - 2007-07-28 : 17:24:02

thanks alot...

scope
Starting Member

6 Posts

Posted - 2007-07-28 : 17:28:35

If i want to sort the sumofpt i tried to add ORDER BY Sum(result.pt) DESC; - but then it says:

ADODB.Recordset.1 (0x80004005)
SQLState: S Native Error Code: 1111 [TCX][MyODBC]Invalid use of group function
/test/standings.asp, line 18

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-28 : 17:40:21

Where did you put the ORDER BY statement?
Post full query here.

E 12°55'05.25"
N 56°04'39.16"

scope
Starting Member

6 Posts

Posted - 2007-07-28 : 17:43:27

SELECT SUM(result.pt) as SumOfpt,bruger.navn FROM result inner join bruger on bruger.id=result.uref where sref = 4 GROUP BY bruger.navn,uref, bruger.id ORDER BY ssum(result.pt);

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-28 : 17:57:41

SELECT bruger.navn, SUM(result.pt) as SumOfpt
FROM result
inner join bruger on bruger.id = result.uref
where sref = 4
GROUP BY bruger.navn, bruger.id
ORDER BY sum(result.pt)

uref and bruger.id is the same value, so no need to use both.

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-29 : 00:20:55

Doesn't make sense to group on bruger.id but not return the value - the client won't be able to tell what the values refer to.
try
SELECT bruger.navn, bruger.id, SUM(result.pt) as SumOfpt
FROM result
inner join bruger
on bruger.id = result.uref
where sref = 4
GROUP BY bruger.navn, bruger.id
ORDER BY sum(result.pt)

if that doesn't work try
ORDER BY SumOfpt

==========================================
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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-29 : 04:37:38

Yes, nr, it does. I often GROUP BY both name and ID, because there are lot of names like "Per Carlsson" but different id.

If nr's suggestion with ORDER BY SumOfPt doesn't work, try ORDER BY 2 -- ordinal number of column starting with 1 for first column.

E 12°55'05.25"
N 56°04'39.16"

scope
Starting Member

6 Posts

Posted - 2007-07-29 : 15:49:18

nr - it works on my asp-server - but on the unix it says:

ADODB.Recordset.1 (0x80004005)
SQLState: S Native Error Code: 1111 [TCX][MyODBC]Invalid use of group function
/test/standings.asp, line 18

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-29 : 22:09:58

>> Yes, nr, it does. I often GROUP BY both name and ID, because there are lot of names like "Per Carlsson" but different id.

You would just group by id if you didn't put the name in the resultset.
If you put the name but not the id in the resultset then you would get multiple rows with the same name but different values which would be meaningless.

==========================================
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.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-29 : 22:11:01

quote:
Originally posted by scope

nr - it works on my asp-server - but on the unix it says:

ADODB.Recordset.1 (0x80004005)
SQLState: S Native Error Code: 1111 [TCX][MyODBC]Invalid use of group function
/test/standings.asp, line 18

What's the database?

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources