How to count number of fields by specific column in Mysql?

Question

Consider the schema with the tables :

Products

enter image description here

And Family

enter image description here

How can I get the number of products of each family_code ?

This :

SELECT count(family_code)
FROM products;

returns the number of family_code record in the Products table ,how can I alter that to find each family_code with its number of products ?

Thanks

For instance

enter image description here

1234 has 2

2345 has 1

12345 has 1

AgRizzo · Accepted Answer · 2014-03-26 22:35:24Z

1

By starting with the family table and LEFT JOINing to the products table, this will list all family codes, regardless if they are in the product table or not (i.e., it will list families that have no products)

SELECT f.family_code,f.family_name, COUNT(p.prod_id) AS number_of_products
FROM family AS f
LEFT JOIN products AS p
 ON f.family_code = p.family_code
GROUP BY f.family_code, family_name;

answered Mar 26, 2014 at 22:35

AgRizzo

5,2711 gold badge15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bohemian · Accepted Answer · 2014-03-26 22:33:10Z

1

Use COUNT(*) grouped by family_code:

select family_code, count(*) product_count
from products
group by family_code

answered Mar 26, 2014 at 22:33

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Collectives™ on Stack Overflow

How to count number of fields by specific column in Mysql?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related