在mySQL select语句中查找正确的计数语法

Question

Goal: To have a table of SIC codes with their descriptions (joined via another table) and count the number of instances of each DISTINCT SIC code used. Ideal outcome below...followed by current mySQL select statement:

SIC Code | Description | Count of SIC Code
0001     | Desc A      | 10               
0002     | Desc B      | 50               
000N     | Desc N      | 80

Current SQL Statement, but causing error notice:

$sql = "SELECT DISTINCT company_list.SIC_Codes, sic_codes.SIC_desc, COUNT(*) as sicCount, FROM company_list LEFT JOIN sic_codes ON company_list.SIC_Codes = sic_codes.SIC_code;";

Any ideas or perspective on which count syntax to use to obtain the example above?

Thank you.

Answer 1

Try making a subquery for COUNT something like this.

$sql = "SELECT DISTINCT company_list.SIC_Codes, sic_codes.SIC_desc, (SELECT COUNT(*) FROM company_list LEFT JOIN sic_codes ON company_list.SIC_Codes = sic_codes.SIC_code) as sicCount, FROM company_list LEFT JOIN sic_codes ON company_list.SIC_Codes = sic_codes.SIC_code";

Instead of putting the COUNT as field, try making a subquery for it.
Change COUNT(*) to (SELECT COUNT(*) FROM company_list LEFT JOIN sic_codes ON company_list.SIC_Codes = sic_codes.SIC_code)