Count Number of Elements From a DISTINCT query-CodePudding

I have this tbl_transactions, this is the table

the image is the result after querying

SELECT DISTINCT vehicle_type_name, vehicle_plate_number FROM tbl_transactions

now, I need to count the number of 2 Wheeler, 4 Wheeler, etc... can someone help me to this. I've used the combination of DISTINCT and WHERE clause, but it shows error in query.

CodePudding user response：

You can select in a subquery only the distinct values and count it on the outer query. If this is what you are trying please try and let me know if it helps and feel free to ask if something is unclear.

SELECT vehicle_plate_number,count(*) as number_of_rows
FROM (
       SELECT vehicle_type_name,
              vehicle_plate_number
       FROM tbl_transactions
       GROUP BY vehicle_plate_number,vehicle_type_name
) as t1 
GROUP BY vehicle_plate_number;

Demo: https://www.db-fiddle.com/f/vwi9bkdUVPoZeURdNAEoDX/4