Count & Group By Conflict

Question

I'm having a conflict in the following query:

SELECT COUNT(*) AS poststotal FROM posts GROUP BY category

The following query returns the poststotal containing the number of categories instead of the total posts count groupped by categories.

So:

TABLE: posts(title,category)
----------------------------
foo1  |  art
foo2  |  politic
foo2  |  politic
foo3  |  fun

Will return:

Instead of:

3 posts

How to deal with that?

It returns `4`? Is `categories` a different column than `category`? — hangy, Jan 01 '11 at 22:10
Noone can understand what you're asking. Three people so far have given correct answers to how they are interpreting this confusing question. Please clarify what you are asking in terminology that people can understand. — hobodave, Jan 01 '11 at 22:37

ajreal · Accepted Answer · 2011-01-01T22:31:58.133

3

/* get count for each category */
SELECT category, COUNT(*) AS poststotal FROM posts GROUP BY category;

your original query should return 3, it could be caused by whitespace stored in column category, try

/* verify */
select title, length(category) from posts;

/* ensure whitespace trimmed */
SELECT COUNT(*) AS poststotal FROM posts GROUP BY trim(category);

hmm, thanks, but i want to get the total number of all posts but only one post of each category :) also the categories are fully trimmed

is quite different from what you had described

select 
  *
from posts 
group by category

/* the above will return single posts for each category */

edited Jan 01 '11 at 22:31

answered Jan 01 '11 at 22:11

ajreal

46,720
11
89
119

hmm, thanks, but i want to get the total number of all posts but only one post of each category :) also the categories are fully trimmed – CodeOverload Jan 01 '11 at 22:16
Shouldn't his original return something like `[1, 2, 1]` or `[1, 1, 1, 1]` if there is a whitespace in a category which makes them not match? The `GROUP BY category` should still be applied, despite the `category` column not being `SELECT`ed, right? – hangy Jan 01 '11 at 22:19
@hangy - OP said will return 4 posts – ajreal Jan 01 '11 at 22:27
When i fetch it i only get One category, but the poststotal contains 4 :( which means they are matched successfully – CodeOverload Jan 01 '11 at 22:29
@David - `"category"!="category "`, the fetch should return two categories instead, which mean you category indeed contains whitespace – ajreal Jan 01 '11 at 22:33

score 1 · Answer 2 · answered Jan 01 '11 at 22:29

1

I am still not sure if I understood your question 100%, but how about that?

SELECT COUNT(*)
FROM poststotal
WHERE category IN
(
    SELECT category
    FROM poststotal
    GROUP BY category
    HAVING COUNT(*) > 0
)

answered Jan 01 '11 at 22:29

hangy

10,765
6
43
63

i see that you query is so hungry for memory ;) – CodeOverload Jan 01 '11 at 22:31
@David - `IN` with a subquery [is completely inefficient in MySQL](http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-slower-than-in-sql/3417190#3417190). Try `SELECT COUNT(*) FROM poststotal WHERE category IN (SELECT category FROM (SELECT category FROM poststotal GROUP BY category HAVING COUNT(*) > 0) as D)` [as discussed here](http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/) – Martin Smith Jan 01 '11 at 23:40

oezi · Answer 3 · 2011-01-03T08:01:04.327

0

if you don't want the count to be grouped by categorys, don't groub by. do it like this:

SELECT COUNT(*) AS poststotal FROM posts

(it's that easy, i think i missunderstood your question... but i'll give it a try)

edited Jan 03 '11 at 08:01

answered Jan 01 '11 at 22:09

oezi

51,017
10
98
115

Noo! that missed the whole point, i want to fetch the number of posts under unique categories, there must be a group by, i've successed at it with mysql_num_rows but i prefer to get this done using count for performance issues – CodeOverload Jan 01 '11 at 22:10

Count & Group By Conflict

3 Answers3