I'm trying to count the total number for makes 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, which i tried to [modify this](https://stackoverflow.com/questions/17065325/mysql-counting-multiple-columns-in-group-by-clause) so i can group by 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

```

Right now i get this error.

1052 - Column 'make' in field list is ambiguous
===============================================

Output should look like this

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

```

Basically it should get total number of makes, in dealership table, and get total number of makes in vechile\_ads table (group by make and dealer\_id)

Any idea what i'm doing wrong here.

**UPDATE:**
[SQL FIDDLE](http://www.sqlfiddle.com/#!2/d228d6/1)

In the fiddle above, `NUMADS` for `BMW` should be 1 (group by `dealer_id`) and the columns returned should be `make`, `make_code`, `count` where `count` is NUMADS + NUMDEALER