CODEWITHSUNDEEP
mysql
CodeOverload
CodeOverload

Reputation: 48525

Count & Group By Conflict

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:

 4

Instead of:

3 posts

How to deal with that?

Upvotes: 1

Views: 702

Answers (3)

oezi
oezi

Reputation: 51817

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)

Upvotes: 0

ajreal
ajreal

Reputation: 47321

/* 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 */

Upvotes: 3

hangy
hangy

Reputation: 10859

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
)

Upvotes: 1

Related Questions