multiple COUNT's

[Anon]

I would like to count id's in multiple tables in the same database. I have a basic select statement and its outputting numbers however the numbers are incorrect.

Here's my select:

$result = mysql_query("SELECT ph.agent_id, ph.home_id, COUNT(ph.agent_id) as img_count, h.id, COUNT(h.owner) as listings FROM home_photo ph, homes h
WHERE (ph.agent_id = $current_user)
GROUP BY h.owner;");

$img_count and $listings should be different however they are displaying the same number and neither are correct numbers.

I checked at mysql.com for docs on COUNT and none of them have multiple counts. Is it even possible?

Any ideas on how to go about counting multiple fields is greatly appreciated!

Thanks.

Jason.

[Anon]

The query you're attempting is beyond the bounds of SQL. In order to get a COUNT(), ust it in it's own function. You shouldn't even put it with a select of regular fields.

[[deleted]]

You can do multiple counts, as long as you use proper SQL.

This query fails majestically because of MySQL crap interpretation of the GROUP BY clause.

In real SQL you cannot select a column if you only group by another column.

SELECT b FROM table GROUP BY a; // Not possible.

And why not? because the group by grabs sets of rows where the data in the group-by clause is the same.
That means that all other columns in those rows may have different values.
If you select any of those other columns, MySQL will return 'a' value from 'a' row.

imagine this table:

A B

1 1
1 2
1 8

2 10
2 4

3 7
3 2
3 8

you can see three groups of rows that have the same value for A.
This data is 'GROUP BY a'
Now if I tell MySQL to
SELECT b FROM table GROUP BY a;
It will show me one value of b for every unique value of a.
But which one should it show? There are several possibilities, and the query doesn't define which one I want.

In this case, MySQL F's up very badly by simply displaying the first row, where it should refuse to run the query because it cannot know which value to return.

[Anon]

So from the sounds of it I'm out of luck... right?

Thanks for your explanations.

Jason.

[[deleted]]

You're not out of luck (yet) you just have to rewrite the query.

[Anon]

I pretty much a newbie with php/mysql... would it be possible for you to give me a start at the query?

Thanks.

Jason.

[[deleted]]

Sure, if you can tell me a bit more about what kind of data you have and what you want to do with it.

[Anon]

Basically, I am working on a realty listings database and on the agent's admin page, I want to display a count of the number of listings an agent has (table "homes") and how many house images the agent has (table "home_photo). I'd like to display this information on the pagetop.php template that gets added to the agent admin page.

Here's the main part of the "homes" table structure:

CREATE TABLE homes (
id int(11) DEFAULT '0' NOT NULL auto_increment,
owner int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (id),
KEY owner (owner),
);

Here's the main part of the "home_photo" table structure:
CREATE TABLE home_photo (
id smallint(6) DEFAULT '0' NOT NULL auto_increment,
agent_id smallint(6) DEFAULT '0' NOT NULL,
home_id smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);

home_photo.agent_id relates to homes.owner and home_photo.home_id relates to homes.id

So I'd like to count homes.owner AND home_photo.agent_id.

Thank you for your time and effort. If you need more information, please let me know and I'll post it right away.

Jason.

[Anon]

i have the same problem.. but mine is somewhat diff. i have a lot of other tables w/ have reference to each other. i need to sort the entries per station.

it's basically like this

station.stationid LINKED to trace.stationid
then
trace.po linked to records.po
then

(i need to count occuring products in records)
but view it per station.
possible?