struggling to count with a join...

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

SQL Server 2000 Forums

SQL Server Development (2000)

struggling to count with a join...

Author

Topic

squarefish
Starting Member

28 Posts

Posted - 2005-12-01 : 10:46:17

Hi
I have two tables, one with all units, one with some units, but listed more than once - kind of like orders

I want to link them to show all the units, and then how many occurances there are in an asp page.

e.g.

unitsno qty
unit1 0
unit2 3
unit3 4
unit4 1
unit5 0

I have had a go at the SQL statement, but I keep getting an error;
"You tried to execute a query that does not include the specified expression 'id' as part of an aggregate function."

Here is the sql I tried
SELECT tblUnitDetails.id, tblUnitDetails.fldtrailerno, tblJobs.id, COUNT(tblJobs.id) as num_recs FROM tblUnitDetails LEFT JOIN tblJobs ON tblUnitDetails.id = tblJobs.fldMagic WHERE " & session("accNo") & " = '" & session("customer") & "' ORDER BY fldTrailerNo

where am I going wrong??

Thanks

Richard

14437
Starting Member

7 Posts

Posted - 2005-12-01 : 11:00:17

squarefish,

Because you are using the Count aggregate function, SQL requires you to use the GROUP clause as well. Re-writing the statement, it should work as follows:

SELECT
tblUnitDetails.id, tblUnitDetails.fldtrailerno, tblJobs.id, COUNT(tblJobs.id) as num_recs FROM tblUnitDetails LEFT JOIN tblJobs ON tblUnitDetails.id = tblJobs.fldMagic WHERE " & session("accNo") & " = '" & session("customer") & "'
GROUP BY tblUnitDetails.id, tblUnitDetails.fldtrailerno, tblJobs.id
ORDER BY fldTrailerNo

squarefish
Starting Member

28 Posts

Posted - 2005-12-01 : 11:31:32

that is very close, but I now get the following results

unit1 0
unit2 1
unit2 1
unit2 1
unit3 1
unit4 0

Can this be added up somehow?

Thanks again

Richard

14437
Starting Member

7 Posts

Posted - 2005-12-01 : 12:39:42

Richard,

Looks like you are using three fields and the Count function in your query. Not knowing the data in your table very well, I would say that the best option would be to try to use just the fields you need. The extra field that is not being shown in your results is probably what is making each record returned a unique row. If you remove that field then SQL can count based upon the field where you have the unitnumbers listed.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-02 : 01:03:13

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-02 : 01:16:32

I think this is what you require
SELECT
tblUnitDetails.fldtrailerno,tblUnitDetails.id, COUNT(tblJobs.id) as num_recs FROM tblUnitDetails LEFT JOIN tblJobs ON tblUnitDetails.id = tblJobs.fldMagic WHERE " & session("accNo") & " = '" & session("customer") & "'
GROUP BY tblUnitDetails.id,tblUnitDetails.fldtrailerno
ORDER BY fldTrailerNo

squarefish
Starting Member

28 Posts

Posted - 2005-12-02 : 03:06:15

Brilliant.

Thank you all for you input and help on this.

Richard

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources