MySQL display total count of values under limit

user_15795053 0
Viewed 48

I'm pretty new to SQL and am currently trying to run a query which will return the total count of a value under a certain limit (using phpmyadmin).

Say we've got a table like so:

CarID |  Car | OwnerID
-------------------
1     | Name |   1
2     | Name |   3
3     | Name |   2
4     | Name |   1

Now I would like to be able to get the total count of cars a owner has if it's under let's say 2 - to then get this:

OwnerID|  TotalCars |
---------------------
2      |      1     | 
3      |      1     |

How would I accomplish this? My situation is slightly different than the example I gave but it's fundamentally the same exact goal but just different numbers and more records.

When I try, those with more than the number I wish to see return with zero as their value??

(My code) Code giving me trouble

(My result) ID 6 has 3 properties so it shouldn't be showing me it at all and I don't understand why it's returning it as 0

phpmyadmin mysql
1 Answers

You can use GROUP BY COUNT and HAVING

CREATE TABLE Table1
    (`CarID` int, `Car` varchar(4), `OwnerID` int)
;
    
INSERT INTO Table1
    (`CarID`, `Car`, `OwnerID`)
VALUES
    (1, 'Name', 1),
    (2, 'Name', 3),
    (3, 'Name', 2),
    (4, 'Name', 1)
;

SELECT `OwnerID`, COUNT(*) as countr
FROM Table1
GROUP BY `OwnerID`
HAVING countr < 2

OwnerID | countr
------: | -----:
      3 |      1
      2 |      1

db<>fiddle here

user_51935360
Related