Mysql group by 2 fields

Question

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 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

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

Answer 1

you are simply declaring 2 columns named make in you joins. the first one is:

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 one 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 :)

Answer 2

Try this query :

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; 

Subquery ads will give you the count of distinct dealers who posted ads for a make from vehicle_ads table. Subquery deal will give you the count of makes from dealership table. in the end, Using left outer join join vehicle_make table to ads and deal so that result should contain all id and make and add the counts coming from both the subqueries to get desired result.

Sample SqlFiddle

Note : in your sql fiddle replace count(*) with count(distinct dealer_id) will give you what you desired. check here.

Answer 3

Does this SQL Fiddle give the output you require?

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

You want to get the count from two tales. Here is a way:

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;

As a note: You should name columns consistently in your database. Having make be a string column in one table and an integer foreign key reference in another leads to confusion. I would suggest calling all the foreign key references to vehicle_make.id something like model_id.