MySQL Query for Counts from Multiple Tables

Question

I've started a SQL Fiddle here: http://sqlfiddle.com/#!2/26c26/7

You can view that sample table and see that I've got multiple car models across three tables. I'm trying to get a 6 column layout. The first two columns would be JeepMart1 models, and the count of those models, then the next two columns would the JeepMart2 models, and counts and the last two columns would be JeepMart3 models and counts.

The results of this query from the fiddle table would look like:

Jeep1Models | Jeep1Counts | Jeep2Models | Jeep2Counts | Jeep3Models | Jeep3Counts |
Wrangler          3         Wrangler          1  
Grand Cherokee    2         Grand Cherokee    3         
                            Patriot           1         Patriot           5

The idea is to get a count of each model from each JeepMart.

If it's at all possible, it would be the cherry on top to get the totals of each model like this: (shortened the col names for readability)

Jeep1Mod | Jeep1Cou | Jeep2Mod | Jeep2Cou | Jeep3Mod | Jeep3Cou | Totals
Wrangler       3      Wrangler       1                               4
Grand Cher     2      Grand Cher     3                               5
                      Patriot        1      Patriot        5         6

I am completely open to querying this data in another way, even if it means changing the layout of the results, as long as all the results are there, and are just as easy to use.

Tin Tran · Accepted Answer · 2014-03-06 23:44:15Z

1

you want to COUNT(DISTINCT vin) like below

SELECT J1.model as Jeep1Model,
       COUNT(DISTINCT J1.vin) as Jeep1Counts,
       J2.model as Jeep2Model,
       COUNT(DISTINCT J2.vin) as Jeep2Counts,
       J3.model as Jeep3Model,
       COUNT(DISTINCT J3.vin) as Jeep3Counts,
       COUNT(DISTINCT J1.vin)+
       COUNT(DISTINCT J2.vin)+
       COUNT(DISTINCT J3.vin) as Total
FROM
(SELECT DISTINCT model FROM JeepMart1
UNION SELECT DISTINCT model FROM JeepMart2
UNION SELECT DISTINCT model FROM JeepMart3)A
LEFT JOIN JeepMart1 J1 ON J1.model = A.model
LEFT JOIN JeepMart2 J2 ON J2.model = A.model
LEFT JOIN JeepMart3 J3 ON J3.model = A.model
GROUP BY A.model

sqlFiddle

answered Mar 6, 2014 at 23:44

Tin Tran

6,1763 gold badges25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Query for Counts from Multiple Tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related