Lemon223 Lemon223 - 1 month ago 14
SQL Question

SQL query to count rows that contain value

I'm having some filter on the side of my website for example :

Filter set 1


  • Alfa (?)

  • Mercedes (?)

  • Rover (?)

  • BMW (?)

  • Lancia (?)

  • Fiat (?)

  • Ford (?)



Filter set 2


  • 2 seater (?)

  • 3 seater (?)

  • 4 seater (?)

  • 5 seater (?)



Now I want behind the filter a number that returns how many results will pup-up if selecting that filter.

So there are for example 10 alfa's and 25 2 seaters in the database then behind alfa would stand "(10)" and behind 2 seater would stand "(25)". But if I select the Alfa filter it has to say "(4)" behind the 2 seater filter, because there are only 4 alfa's that also are 2 seaters.

I use this query to filter and return the cars, but this returns a complete array :

SELECT *
FROM cars
WHERE brand LIKE '%{$_GET[brand]}%'
AND seater LIKE '%{$_GET[seater]}%'


How can I alter it to return just a number?

Thank you.

Answer

If I understand correctly, you want to fill in the "?"s. This would involve a separate query for each of the lists:

SELECT brand, count(*) as cnt
FROM cars 
GROUP BY brand; 


SELECT seater, count(*) as cnt
FROM cars 
GROUP BY seater; 

Your query returns the combinations selected by the user -- each row or the count (if you used an aggregation query). But I interpret the question as adding information to the lists themselves.

I should note that you can get the count for each value in the list:

SELECT count(*) as cnt
FROM cars 
WHERE brand = 'Alfa'

However, I advise you to use the GROUP BY for performance reasons -- each query has its own overhead.