MYSQL Select statements using distinct and count

Question

Hello I am a beginner at mysql. I have a table like this

|Item no.|Location|
-------------------
|192     |Japan   |
|882     |Korea   |
|982     |China   |
|111     |China   |
|02      |Japan   |
|03      |Korea   |
|04      |Japan   |

I want the resulting table to be like this

|Location|NumberofItems|
------------------------
|Japan   |      3      |
|Korea   |      2      |
|China   |      2      |

My code doesn't seem to work. Here is my code:

SELECT distinct Location, count(*) as NumberofItems FROM Products inner join Products on Price = Price

juergen d · Accepted Answer · 2015-05-13 09:37:43Z

1

If you group by a specific column you get only the distinct values from that column. Then you can use aggregate functions like count on each group

SELECT Location, count(*) as NumberofItems 
FROM Products
GROUP BY Location

answered May 13, 2015 at 9:37

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

Comments

Nagaraj S · Accepted Answer · 2015-05-13 09:50:32Z

0

You need GROUP BY command.

SELECT distinct location,count(*) as NumberofItems FROM products GROUP BY location;

edited May 13, 2015 at 9:50

Nagaraj S

13.5k6 gold badges35 silver badges53 bronze badges

answered May 13, 2015 at 9:48

Christos Thb

271 silver badge12 bronze badges

Collectives™ on Stack Overflow

MYSQL Select statements using distinct and count

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