How to get count of grouped rows

Question

I need to get dealers ids, which have multiple car's vendors. By example tables, I need to get id 3. I'm stuck on this query:

SELECT COUNT(car.id), dealers2cars.dealer_id, car.vendor_id FROM car, dealers2cars
WHERE 
dealers2cars.`car_id` = car.id
GROUP BY car.vendor_id, dealers2cars.`dealer_id`

But it get wrong result (all dealers and count of their cars grouped by vendor).

count dealer_id vendor_id
2,1,1 
1,3,1 
3,2,2 
1,3,2

This is example database tables:

CREATE TABLE `car` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `car` */

insert  into `car`(`id`,`title`,`vendor_id`) values 
(1,'F1',1),
(2,'480 Italia',1),
(3,'New Beatle',2),
(4,'Scirocco',2),
(5,'Golf',2);

/*Table structure for table `dealers2cars` */

CREATE TABLE `dealers2cars` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dealer_id` int(11) DEFAULT NULL,
  `car_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `dealers2cars` */

insert  into `dealers2cars`(`id`,`dealer_id`,`car_id`) values 
(1,1,1),
(2,1,2),
(3,2,3),
(4,2,4),
(5,2,5),
(6,3,1),
(7,3,5);

What results are you getting? What are the expected results? — Vulcronos, Jul 11 '14 at 13:20

score 1 · Answer 1 · answered Jul 11 '14 at 13:22

1

I believe, you need an INNER JOIN (as opposed to a cross join you used)

SELECT 
    COUNT(car.id) as carCount, 
    dealers2cars.dealer_id, 
    car.vendor_id 
FROM 
    car
    INNER JOIN dealers2cars ON car.id=dealers2cars.car_id
GROUP BY 
    car.vendor_id, 
    dealers2cars.`dealer_id`

answered Jul 11 '14 at 13:22

J A

1,776
1
12
13

Nope, this get the same result like my query. – ilya iz Jul 11 '14 at 13:30
@JA The `JOIN` used in the example query is achieved in the WHERE clause, but has the same effect as an `inner join`. – SchmitzIT Jul 11 '14 at 13:31

SchmitzIT · Accepted Answer · 2014-07-11T13:43:40.670

0

If I understood the question correctly, this should do the trick for you:

SELECT 
  count(vendor_id)
, dealers2cars.dealer_id
FROM dealers2cars JOIN car ON dealers2cars.dealer_id = car.id
GROUP BY dealer_id
HAVING COUNT(vendor_id) > 1

Output:

COUNT(VENDOR_ID)    DEALER_ID
2                   1
3                   2
2                   3

Update: This is most likely the qudery you need:

SELECT 
COUNT(DISTINCT(vendor_id)),dealer_id
FROM dealers2cars JOIN car ON dealers2cars.car_id = car.id
GROUP BY dealer_id
HAVING COUNT(DISTINCT(vendor_id)) > 1

It pulls the dealers that have cars from more than 1 unique vendor.

edited Jul 11 '14 at 13:43

answered Jul 11 '14 at 13:24

SchmitzIT

9,227
9
65
92

Not exacly. Your query get count of cars by dealer. I need to get dealers which has cars with different makes (vendors) – ilya iz Jul 11 '14 at 13:28
What's the output you're expecting, exactly? Why is what you get wrong? – SchmitzIT Jul 11 '14 at 13:33
I need to get: vendor_id 3, which has 2 cars of different vendors (VW and Ferrary) – ilya iz Jul 11 '14 at 13:40
@ilyaiz - I figured that out while you typed. Check the update in my query :) The only real change is adding `DISTINCT` to the `COUNT to weed out the dealers having multiple cars from the same vendor. – SchmitzIT Jul 11 '14 at 13:44

How to get count of grouped rows

2 Answers2