Combo Grouping

[Alexandre Saillant]

Hello,

I have 2 database, One with vehicule data and another with All the
option each vehicule has. I want to build a report that will show me
the most popular GROUP of option.

SO,

Table 1:
CarID Color Engine etc..
1111 Reg V6
2222 Green V6
3333 Blue V6
etc. ... ...

Table2:
CarID Option
1111 A
1111 B
1111 C
2222 A
2222 C
3333 B
3333 C

So I want to build a query that will group my data like this:

OptionCombo NBcars
AB 10
AC 5
ABC 8
BC 12

etc.

How the hell is this possible? note that option A and B must be the
same that B and A!!! So mergin all option has text one on back of the
other wont work.


help on this please!

 

[Michel Walsh]

Hi,


A possible solution, assuming you already know all the possible options,
and that there are less than 31 of them, and none is duplicated, in table2,
for a given carID:

SELECT SUM(Option='A' + 2*( Option='B' + 2* (Option = 'C' + 2*
(Option='D') )) As Options

FROM myTable1 INNER JOIN myTable2
ON table1.CarID = table2.CarID

GROUP BY table1.CarID



Note that Options return an integer accordingly to the presence or absence
of given options:


Options A B C D
0 0 0 0 0
-1 -1 0 0 0
-2 0 -1 0 0
-3 -1 -1 0 0
-4 0 0 -1 0
-5 -1 0 -1 0
-6 0 -1 -1 0
-7 -1 -1 -1 0
-8 0 0 0 -1
-9 -1 0 0 -1
....
-15 -1 -1 -1 -1

With the previous query as a saved query, qt1, you can then write:


SELECT Options, COUNT(*)
FROM qt1
GROUP BY Options
ORDER BY COUNT(*) DESC




Hoping it may help,
Vanderghast, Access MVP

 

[Alexandre Saillant]

Hummm,

Your idea is great but unfortunatly, I have up to 331 different
options. They rarely will be more that 3-4 options per cars but the
list is endless.

There must be a way throug a union query or something.

any ideas?

 

[Michel Walsh]

Hi,


There are many interesting "limits", like

SELECT (Option='A'), (Option='B'), ... , COUNT(*)
FROM
GROUP BY (Option='A'), (Option='B'), ...


but the SELECT is limited to 255 fields, and the GROUP BY to 32, I think,

Another solution would be based on a LIST concatenation, to build 'ABCD', a
string of selected option, but now, such a string, a varchar is limited to
255 characters, too! No good.


So I decide to take a different approach, a "relational" one.

===============
SELECT a.CarID As thisCar, b.CarID As thatCar
FROM myTable As a INNER JOIN myTable As b
ON a.option = b.option
GROUP BY a.carID, b.carID
HAVING COUNT(*) = (SELECT COUNT(*) FROM myTable As c
WHERE c.carID=a.carID)
AND COUNT(*) = (SELECT COUNT(*) FROM myTable As d
WHERE d.carID = b.carID)
================

seems, at first glance (***not tested***) to associate together cars having
exactly all the same options. The association is indirect, it is a list, two
by two, like:

thisCar thatCar
-----------------------
car1 car1
car1 car3
car1 car4
car2 car2
car3 car1
car3 car3
car3 car4
car4 car1
car4 car3
car4 car4

which would tell us that car1 has the same options than car1, car3, and
car4.
Save that query, Qs, then


SELECT thisCar, COUNT(*)
FROM qs
GROUP BY thisCar


tell us how popular are the options of each car.


car1 3
car2 1
car3 3
car4 3

so, as example the combo of options on car1 is exactly the same on two other
cars ( 3 with itself). We then know which CAR have the most popular "set"
of options, or combo as you said.

A problem is to eliminate "symmetry", that is, if car1 has the same options
than N other cars, then N other records would say exactly the same thing.
That is not good, for stats neither for presentation. We have to eliminate
those N other cars. We don't have their options, but the following should
do:

==============
SELECT w.thisCar, COUNT(*)
FROM qs As w
WHERE thisCar <= ALL (SELECT z.thatCar
FROM qs As z
WHERE z.thisCar = w.thisCar)

GROUP BY w.thisCar
===============



Note that someone can replace
<= ALL( SELECT z.thatCar FROM ...
with
= (SELECT MIN(z.thatCar) FROM ...
here, since there is no NULL implies.

Indeed, it is based on that if we have

car4 car1
car4 car3
car4 car4


then, it fails the test car4 = MIN(car1, car3, car4), and only one car of
the "group of car with the same combo", should ever satisfy the test.

So, that last query, qs2, give us the popularity of a combo of options, you
can add an ORDER BY on the COUNT, and a representative car having this combo
of options (BUT does not list the said options, part of the "combo"). Thanks
to relationship based solution, we almost completely evacuate the need to
deal with the options themselves. It is a little bit as if we "borrowed" a
carID to make it a "combo id". We do that every day in speech, so, why not
in our SQL Since you have a representative carID, you can get the
individual options in this "combo", through a join with table2.

Is that close enough to what you want?



Hoping it may help,
Vanderghast, Access MVP