[RESOLVED] Using COUNT Twice Within Query?

joe_C_nice

I've got a tricky little problem. What I want to do is run query that counts the total rows for each row with identical entries.

Within this same query I need to get the total number of rows, regardless of the entry.

Here's how it were to look if I were using two queries.

// first query
mysql_query("select COUNT(*) from members GROUP BY state");

// second query
mysql_query("select COUNT(*) from members");

//I would like to combine these two queries into one

thorpe

and your questions is?

planetsim

I think he wants the two queries into one, which the way its setup wouldnt be possible as the GROUP BY would get the results and COUNT would just do the count twice (of course MySQL im pretty sure would store the initial value so when an identical function call wouldnt require to be executed but rather the result returned from the previous).

joe_C_nice

I was told this could be done.

Anyone ever encounter such a problem.

laserlight

I was told this could be done.

Ask whoever told you so for the solution.

Anyway, I think you do have to use 2 queries - unless you observe that the results of the query to obtain the total number of rows can be obtained by using PHP to sum the results of the other query.

Sxooter

How about something like:

select state, COUNT() from members GROUP BY state" UNION
"select 'total' as state, COUNT() from members

Drakla

Sxooter wrote:
How about something like:

select state, COUNT() from members GROUP BY state" UNION
"select 'total' as state, COUNT() from members

CHEAT!!!

joe_C_nice

That did it thank you.

Sxooter

Drakla wrote:
CHEAT!!!

Bwahahaha. Quake before my mighty UNION skills.

Oh, and by the way, you might want union all on that one... Just saying.

😃