Count unique data within a category

Question

I am looking for help in trying to solve an issue with a query.

Query:

SELECT distinct BRAND,(select count(distinct VIN) from STOCK) as STOCK ITEM COUNT
from STOCK

What I am trying to achieve is to display the brand and unique count of all VIN numbers which are located in each brand.

For some reason when I run the above query each brand eg, Ford,GM,TOYOTA, etc display the same count .

Students simply shouldn't learn the `distinct` keyword until they full understand `join`, `group by`, and `subqueries`. — Gordon Linoff, May 20 '15 at 11:30
@GordonLinoff talking about group by and subqueries is it possible to use a group by in a subquery, as I am having the same issue with another query I am doing where the calculated average amount is the same each row — ITworldR, May 20 '15 at 11:52

score 4 · Accepted Answer · edited May 20 '15 at 11:31

4

Or do a simple GROUP BY:

SELECT BRAND, count(distinct VIN) as STOCK_ITEM COUNT
from STOCK
group by brand

edited May 20 '15 at 11:31

Gordon Linoff

answered May 20 '15 at 11:26

jarlh

score 2 · Answer 2 · answered May 20 '15 at 11:25

2

You're currently making a subquery that doesn't take BRAND into account. You want to use GROUP BY:

SELECT Brand, Count(*)
FROM Stock
GROUP BY Brand

answered May 20 '15 at 11:25

David Hedlund

2 Answers2