count / limit

[astralaaron]

Hi, i posted here: this topic about limiting 3 results from each different user who has products. I got it working, using 'UNION ALL' to join together the separate queries, limiting each one to 3.Now the problem that i am facing is i need to also return the total number of rows to display a "3 out of 10 products" type of thing. I would like to make it happen in the same query if possible. Or is it impossible?The query is along these lines:

(SELECT `tablea`.`field1`, `tablea`.`field2` FROM `tablea` WHERE `tablea`.`id`=1 LIMIT 0,3) UNION ALL (SELECT `tablea`.`field1`, `tablea`.`field2` FROM `tablea` WHERE `tablea`.`id`=2 LIMIT 0,3)

I need a count in there (EDIT: "in each select") which counts ALL not only the 3 from the LIMIT, can it be done?

[astralaaron]

[justsomeguy]

Couldn't you just add a count field in the field list? Have you tried that?

[astralaaron]

Couldn't you just add a count field in the field list? Have you tried that?

Well, i added 'count(*) as num'. And you are right it does count the total number. But only for the first set of them, and it retrieves 1 product from each user after adding that..Do you have any idea whats happening?

[justsomeguy]

I'm not sure why adding count would cause it to limit the number of results. It should still return the same number of rows.

[astralaaron]

it's strange, if i take out the count it outputs 3 from each. if i put the count back in just 1 from each.

[justsomeguy]

You may want to use group by to group by all of the fields other than the count.

[astralaaron]

i ran the query it outputs directly on the database, and it actually does count for each, but only once for each user, it doesnt do it 3 times for each of the products of that user

[astralaaron]

i just tried adding GROUP BY to the userid field, but it's the same result. the example i found here http://www.w3schools.com/sql/sql_groupby.asp seems close but slightly different than my problem... its backwards even. when they group, they group down the results from 6 to 3.

[astralaaron]

i kind of see where there is a problem.. when there is 2 users, it UNION ALL's together 2 queries.. with 2 count functions. each of those queries returns a set of 3 results without the count function. but only 1 when it does the count. the count needs to be added to each result somehow

[astralaaron]

seems like something similar to this example i found might be the answer...:select count(*) from (select .......)unionselect count(*) from (select.....);Sub queries. but i cant make sense of how to write it.

[astralaaron]

I just decided to go with 2 separate queries, have a deadline - thanks anyway