counting entries in a column

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

counting entries in a column

Post by invisibled »

Ok so i am listing entry's in this column and grouping them so its only displaying a category once using:

SELECT category FROM ida_projects GROUP BY category

beside each entry i want to have the number of entries that match that. So if there is 4 entries that are the category "static" then it will print a "4"

i think im close to getting it but i can never seen to get it right so i must be overlooking somthing. Any suggestions.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Code: Select all

SELECT
  ip.category,
  (select count(*) from ida_projects ip2 where ip.category = ip2.category) as category_count
FROM
  ida_projects ip
GROUP BY
  ip.category
For performance you'll probably want to count by category_id rather than the category name, if you're able to. Regardless, the field should be indexed.
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Post by invisibled »

thanks so much!

im glad it was alot easyer than i expected!
Post Reply