Mysql Query Using Where and Group by Clause
I Have the Following Table. Mysql> Select * from Consumer9; +------------+--------------+-------------------+ | Service_Id | Service_Type | Consumer_Feedback |...
I have the following table.
mysql> select * from consumer9;
+------------+--------------+-------------------+
| Service_ID | Service_Type | consumer_feedback |
+------------+--------------+-------------------+
| 100 | Computing | -1 |
| 35 | Printer | 0 |
| 73 | Computing | -1 |
| 50 | Data | 0 |
+------------+--------------+-------------------+
I want to use GROUP BY clause in my project. I am getting an error when I am using the query:
SELECT Service_ID, Service_Type, SUM(consumer_feedback)
FROM consumer9
GROUP BY Service_ID
WHERE Service_Type=Printer;
Error
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where Service_Type=Printer' at line 1
2 Answers
The following query should work.
select Service_ID, Service_Type, sum(consumer_feedback)
from consumer9
where Service_Type=Printer
group by Service_ID, Service_Type;
Remember, the where clause goes before the group by clause and all non-aggregated terms in the select part will have to be present in the group by clause.
Use the having clause instead of where
SELECT Service_ID, Service_Type, SUM(consumer_feedback)
FROM consumer9
GROUP BY Service_ID
HAVING Service_Type='Printer';
Regards.
Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.
