Counting rows with different values

Question

I have a table in mysql and I want this output:

HNSHAK  MOBILE COMPUTING =13
HNSHAK  STORAGE           =2

the product group will change each time I execute.

Table:

Productgroup    Created By
MOBILE COMPUTING    HNSHAK
MOBILE COMPUTING    HNSHAK
MOBILE COMPUTING    HNSHAK
MOBILE COMPUTING    HNSHAK
STORAGE             HNSHAK
MOBILE COMPUTING    HNSHAK
STORAGE             HNSHAK
MOBILE COMPUTING    HNSHAK
MOBILE COMPUTING    HNSHAK
MOBILE COMPUTING    HNSHAK
MOBILE COMPUTING    HNSHAK
MOBILE COMPUTING    HNSHAK
MOBILE COMPUTING    HNSHAK
MOBILE COMPUTING    HNSHAK
MOBILE COMPUTING    HNSHAK

Mahmoud Gamal · Accepted Answer · 2013-04-14 08:23:50Z

1

Your query should be this way:

SELECT `Created By`, ProductGroup, COUNT(*) `Count`
FROM tablename
GROUP BY `Created By`, ProductGroup;

using a GROUP BY Created_By, ProductGroup and the aggregate function COUNT.

This will give you:

Created By  ProductGroup           Count
HNSHAK      MOBILE COMPUTING        13
HNSHAK      STORAGE                 2

edited Apr 14, 2013 at 8:23

answered Apr 14, 2013 at 8:18

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

Comments

Ejaz · Accepted Answer · 2013-04-14 08:25:32Z

0

SELECT `Created_By`, `Productgroup`, COUNT(`Productgroup`)
FROM `table_name`
GROUP BY `Productgroup`

You should use GROUP BY here. You should also keep in mind that if all of the values in Created By are not same (like one of them is, say, COMPANY1) then either you want to ignore the Productgroup and select first value of Productgroup that appears in the table or you'll have multiple rows of a Productgroup for each _manufacturer. To achieve the latter, you'll use GROUP BY this way

GROUP BY `Created_By`, `Productgroup`

edited Apr 14, 2013 at 8:25

answered Apr 14, 2013 at 8:19

Ejaz

8,8723 gold badges38 silver badges52 bronze badges

Collectives™ on Stack Overflow

Counting rows with different values

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