StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I find out our customer's favourite brand using a query?
text
Body
copied!<p>I would like to generate a table (or query result) in this form</p> <pre><code>+---------------------+---------------------+ | Email | Favourite Brand ID | +---------------------+---------------------+ | customer@gmail.com | 89 | +- -+- -+ | another@gmail.com | 193 | +- -+- -+ </code></pre> <p>I have managed to write a query that generates a list of unique brand ID's with customer email addresses and the number of times that customer has purchased that brand. The results look something like this:</p> <pre><code>+---------------------+-----------+---------------+ | Email | Brand ID | CountOfOrders | +---------------------+-----------+---------------+ | customer@gmail.com | 89 | 10 | +- -+- -+- -+ | another@gmail.com | 193 | 32 | +- -+- -+- -+ | duplicate@gmail.com | 20 | 2 | +- -+- -+- -+ | duplicate@gmail.com | 47 | 5 | +- -+- -+- -+ </code></pre> <p>Obviously <code>duplicate@gmail.com</code> has purchased from BrandID 20 twice and BrandID 47 5 times which is why they appear twice. Most customers have purchased from more than one brand.</p> <p>From this information how can I construct a query to get the brand ID they have purchased from the most? I have tried the following but it just times out:</p> <pre><code>SELECT [table1].Email, [table1].Brand, [table1].CountOfBrand FROM [Customer Brand Purchases] AS [table1] GROUP BY [table1].Email, [table1].Brand, [table1].CountOfBrand WHERE [table1].CountOfBrand=( SELECT TOP 1 [table2].CountOfBrand FROM [Customer Brand Purchases] AS [table2] WHERE [table2].Email = [table1].Email ORDER BY [table2].CountOfBrand DESC ); </code></pre> <p>Oh and I have to use Microsoft Access, unfortunately. Thanks.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload