Count() on two tables.

[Anon]

Hi,

I have two tables:
- TBPersons -
Id_Person
Name
*Age

• TBItems -
  Id_Item
  Id-Person
  *Price

Each Person has many items.

I want to perform a SELECT statement that retrieves the NAME, AGE and the total of items for each person.

I've tried to use Count() but I keep getting a syntax error...

Could someone please help me?!

Many thanks,

Jo.

[jjoske]

try:
SELECT TBPersons.Name,TBpersons.Age,COUNT(TBItems.Id_Item)
WHERE TBPersons.Id_Person=TBItems.Id_Person
GROUP BY TBPersons.Id_person

[Anon]

Hrm.

I have a similar sort of thing I'm trying to do, but I keep getting a "Supplied argument is not a valid MySQL result resource".

I'm doing the select, then doing a while statement and printing the results out. (I'm doing this through PHP.)

I have 2 tables: artist and album. artist contains the fields 'artistid' and 'artist', and album contains the fields 'albumid', 'artistid', and 'album'.

My query is this:

SELECT artist.artistid,artist,album,COUNT(artist.artistid)
WHERE artist.artistid = album.artistid
GROUP BY artist.artistid

And I get that error.

Any help is much appreciated! 🙂

[kparker]

For starters, be sure to print the error message that mysql will have returned (using $php_errormsg or the newer mysql error function.) That way the dbms itself will give you a pretty good idea about what went wrong with your attempted query.

Just glancing over what you are trying to do, the problem is likely to be in your attempt to return both a column and its number of occurances in the same query:

SELECT artist.artistid, ... COUNT(artist.artistid)

[jjoske]

Shouldn't the select statement be:

SELECT artist.artistid,artist.artist,album.album,COUNT(artist.artistid)
FROM artist,album
WHERE artist.artistid = album.artistid
GROUP BY artist.artistid

[Anon]

Ah crap. I just tried that, and I get this error:

ERROR 1: Can't create/write to file '/var/tmp/#sqlc9_5d_0.MYI' (Errcode: 13)

I've gotten this error a number of other times while I've been trying to get this damn query working 🙁

[Anon]

Oh, ok.

Is it possible to pipe the output of one query into another, then? (I assume that's the way you'd solve this...)

[jjoske]

I think your problem might be in the set up of MySQL.

Try this link for some more info.
http://www.mysql.com/doc/C/a/Cannot_create.html

[pratheshshah]

hi jo!

try out the following

• TBPersons -
  Id_Person
  Name
  *Age

• TBItems -
  Id_Item
  Id-Person
  *Price

SELECT a.name,a.age,COUNT(b.Id_Person) from Td_Person a,TbItems b
WHERE a.Id_Person = b.Id-Person
GROUP BY a.name,a.age;

this will definitely work

what ever column u want to retruve with count() function then all that column u have to write in Group by clause
otherwise it will give u error

write me it perfomed at [e-mail]

[Anon]

Hi,

Many Thanks.

The error was just on that: 'group by'.

Be cool,

Jo

[Anon]

Aye, I've had a look at that, and tried to fix it, but it didn't appear to work 🙁

And it seems to be only that query that I'm trying to do that doesn't work...all the other ones I've tried (for the rest of my site) work perfectly :\