Mysql group by 2 fields

Question

I am trying to calculate the total number found in my database

I have 3 tables

vehicle_make

+----+------+
| id | make |
+----+------+
|  1 | Audi |
|  2 | BMW  |
+----+------+

vehicle_ads

+----+-----------+----------+------+-----------+
| id | make_code |  model   | year | dealer_id |
+----+-----------+----------+------+-----------+
|  1 |         2 | 5 series | 2010 |        0  |
+----+-----------+----------+------+-----------+
|  2 |         2 | 3 series | 2014 |        20 |
+----+-----------+----------+------+-----------+

dealership

+----+------------+------+
| id | dealername | make |
+----+------------+------+
| 20 | bla bla    |    2 |
+----+------------+------+

Here is my sql query I tried changing this so that I can group make and dealer_id

SELECT j.make AS make, j.id AS id, sum(j.count) AS count FROM 
(
    (
        SELECT v.make, sum(count+dealerCount)
        FROM 
             ( SELECT a.dealer_id, v.make AS make, a.make_code, COUNT(*) AS count
                    FROM `vehicle_make` AS v
                    JOIN `vehicle_ads` AS a
                    ON (v.id=a.make_code)
                    WHERE a.dealer_id>0
                    GROUP BY a.dealer_id, a.make_code
              ) AS g
            JOIN
              ( SELECT a.dealer_id, v.make, a.make_code, COUNT(*) AS dealerCount
                    FROM `vehicle_make` AS v
                    JOIN `vehicle_ads` AS a
                    ON (v.id=a.make_code)
                    WHERE a.dealer_id>0
                    GROUP BY a.dealer_id
              ) AS gl
              ON gl.dealer_id = g.dealer_id
            JOIN
              ( SELECT a.make_code, COUNT(*) AS makeCount
                    FROM `vehicle_make` AS v
                    JOIN `vehicle_ads` AS a
                    ON (v.id=a.make_code)
                    WHERE a.dealer_id>0
                    GROUP BY a.make_code
              ) AS gg
            ON gg.make_code=g.make_code 
    )
    UNION
    (
        SELECT v.make AS make, v.id AS id, COUNT(*) AS count
                    FROM `dealership` AS d
                    JOIN `vehicle_make` AS v
                    ON (v.id=d.make)
                    GROUP BY d.make
    )
) AS j GROUP BY j.make

I am getting this error now.

1052 - The 'make' column in the list of fields is ambiguous

The result should look like this:

+--------+----+-------+
|  make  | id | count |
+--------+----+-------+
| Audi   |  1 |   300 |
| BMW    |  2 |   150 |
| Toyota |  3 |    50 |
+--------+----+-------+

In principle, he should get the total number of blanks in the dealers table, and get the total number of brands in the vechile_ads table (group by make and dealer_id)

Any idea what I'm doing wrong here.

UPDATE: SQL FIDDLE

NUMADS BMW 1 ( dealer_id), make, make_code, count, count - NUMADS + NUMDEALER

Answer 1

:

SELECT make.id,
       make.make,
       ( IFNULL(ads.cout, 0) + IFNULL(deal.cout, 0) ) AS cout
FROM   vehicle_make make
       LEFT OUTER JOIN (SELECT make_code                 AS id,
                               Count(DISTINCT dealer_id) AS cout
                        FROM   vehicle_ads
                        GROUP  BY make_code) ads
                    ON make.id = ads.id
       LEFT OUTER JOIN (SELECT make     AS id,
                               Count(*) AS cout
                        FROM   dealership
                        WHERE  make IS NOT NULL
                        GROUP  BY make) deal
                    ON make.id = deal.id; 

ads count distinct dealers, make from vehicle_ads. deal dealership. , left outer join join vehicle_make table to ads deal, id make , , .

SqlFiddle

: sql (*) ( _), , . .

Answer 2

. :

select vh.make, sum(numads) as numads, sum(numdealer) as numdealer
from ((select make_code as model_id, count(*) as numads, 0 as numdealer
       from vehicle_ads
       group by model_id
      ) union all
      (select make as model_id, 0, count(*)
       from dealership
       group by model_id
      )
     ) c join
     vehicle_make vm
     on c.model_id = vm.id
group by vh.make;

: . make - , . vehicle_make.id - model_id.

Answer 3

SQL Fiddle ?

SELECT vm.`make`,
       vm.`make_code`,
       IFNULL(subq.`num_ads`, 0) AS `count`
FROM `vehicle_make` vm
LEFT JOIN
(SELECT ad.`make_code`, COUNT(*) AS `num_ads`
 FROM
 (SELECT *
  FROM `vehicle_ads`
  GROUP BY `make_code`, `dealer_id`) ad
 GROUP BY ad.`make_code`) subq
ON subq.`make_code` = vm.`id`

Answer 4

2 make in you join. :

SELECT a.dealer_id, v.make AS make, a.make_code, COUNT(*) AS count
FROM `vehicle_make` AS v       ^------------------------------- this field called make
JOIN `vehicle_ads` AS a
ON (v.id=a.make_code)
WHERE a.dealer_id>0
GROUP BY a.dealer_id, a.make_code

and the second is here:

SELECT v.make AS make, v.id AS id, COUNT(*) AS count
                   ^------------------------------------ this field also called make
FROM `dealership` AS d
JOIN `vehicle_make` AS v
ON (v.id=d.make)
GROUP BY d.make

just rename them and run your code .. in general, it looks just fine :)