1

Table: User

Color
1
2
2
2
1
3

Table: Color

ID      Name
1       Orange
2       Yellow
3       Pink
4       Blue

Expected Result

Color      Total
Orange     2
Yellow     3
Pink       1

Mysql Query:

SELECT * FROM User LEFT JOIN Color ON Color.ID = User.Color;

How can I get my expected result? Please help me. and thanks in advance.

Shah Rushabh
  • 51
  • 5

3 Answers3

0
SELECT c.NAME, COUNT(*) AS Total 
FROM Color c
INNER JOIN User u ON (u.Color = c.Id)
GROUP BY c.NAME;
Ashutosh SIngh
  • 931
  • 3
  • 13
  • 26
0

This will helps you to do so. brother :)

SELECT C.Name, COUNT(U.Color) as Total from Color C
            LEFT JOIN User U 
            ON
            U.Color = C.ID
            GROUP by C.ID,C.Name

Thank you!

Unknown_Coder
  • 764
  • 6
  • 24
  • can you explain why you are using LEFT JOIN ?? because it can be done through INNER JOIN and LEFT JOIN is costly. – Ashutosh SIngh Apr 24 '17 at 12:21
  • LEFT JOIN will returns all records from the left table (Color), and the matched records from the right table (User). The result is NULL from the right side,if there is no match. That's why we have to use LEFT JOIN. in this case we can also apply INNER JOIN or Simple JOIN. @AshutoshSIngh – Unknown_Coder Apr 24 '17 at 12:23
  • Please go through it's basic part of query optimization http://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server – Ashutosh SIngh Apr 24 '17 at 12:26
  • yeah. Thank you :) – Unknown_Coder Apr 24 '17 at 12:26
0

@Shah Rushabh use group by U.Color like below and you will get your result:

SELECT C.Name, COUNT(U.Color) as Total from user as U 
inner join color as C on 
U.Color = C.Id GROUP BY U.Color
lazyCoder
  • 2,544
  • 3
  • 22
  • 41