astralaaron Posted September 21, 2010 Share Posted September 21, 2010 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? Link to comment Share on other sites More sharing options...
astralaaron Posted September 21, 2010 Author Share Posted September 21, 2010 Link to comment Share on other sites More sharing options...
justsomeguy Posted September 21, 2010 Share Posted September 21, 2010 Couldn't you just add a count field in the field list? Have you tried that? Link to comment Share on other sites More sharing options...
astralaaron Posted September 21, 2010 Author Share Posted September 21, 2010 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? Link to comment Share on other sites More sharing options...
justsomeguy Posted September 21, 2010 Share Posted September 21, 2010 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. Link to comment Share on other sites More sharing options...
astralaaron Posted September 21, 2010 Author Share Posted September 21, 2010 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted September 22, 2010 Share Posted September 22, 2010 You may want to use group by to group by all of the fields other than the count. Link to comment Share on other sites More sharing options...
astralaaron Posted September 22, 2010 Author Share Posted September 22, 2010 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 Link to comment Share on other sites More sharing options...
astralaaron Posted September 22, 2010 Author Share Posted September 22, 2010 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. Link to comment Share on other sites More sharing options...
astralaaron Posted September 22, 2010 Author Share Posted September 22, 2010 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 Link to comment Share on other sites More sharing options...
astralaaron Posted September 22, 2010 Author Share Posted September 22, 2010 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. Link to comment Share on other sites More sharing options...
astralaaron Posted September 22, 2010 Author Share Posted September 22, 2010 I just decided to go with 2 separate queries, have a deadline - thanks anyway Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.