Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I am reluctant to answer this as the thought of even assisting in the development of a database with a table name [Customer Brand Purchases] makes me feel a little bit sick.</p> <p>My Access SQL is a little rusty but I am 98% certain this will work:</p> <pre><code>SELECT CBP.Email, CBP.Brand AS [Favourite Brand ID] FROM [Customer Brand Purchases] AS CBP INNER JOIN ( SELECT [Email], MAX(CountofBrand) AS [MaxCountofBrand] FROM [Customer Brand Purchases] GROUP BY [Email] ) AS [MaxCBP] ON CBP.Email = MaxCBP.Email AND CBP.CountOfBrand = MaxCBP.MaxCountOfBrand </code></pre> <p>The only draw back is that if a particular customer has ordered 2 brands the same amount of times then it will return 2 rows. You would need additional subqueries with MAX statements in to resolve this.</p> <p>EDIT/ADDENDUM:</p> <p>If it is ABSOLUTELY imperative the query returns 1 result per email address then you need to allow for the scenario where a particular email address has purchased 2 brands and equal amount of times there is no way to establish which or these is favourite as they are joint favourite. If it were me I would deal with this at application level, and Concatenate favourite brands into one string. However, it can be done in SQL just be aware that one or more brands could be hidden using this:</p> <pre><code>SELECT CBP.Email, CBP.Brand AS [Favourite Brand ID] FROM [Customer Brand Purchases] AS CBP INNER JOIN ( SELECT CBP.Email, MAX(CBP.Brand) AS MaxBrand FROM [Customer Brand Purchases] AS CBP INNER JOIN ( SELECT [Email], MAX(CountofBrand) AS MaxCountofBrand FROM [Customer Brand Purchases] GROUP BY [Email] ) AS MaxCBP ON CBP.Email = MaxCBP.Email AND CBP.CountOfBrand = MaxCBP.MaxCountOfBrand GROUP BY Email ) AS MaxCBP ON CBP.Email = MaxCBP.Email AND CBP.Brand = MaxCBP.Brand </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow can I find out our customer's favourite brand using a query?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USGarethD
    1. USGarethD
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow can I find out our customer's favourite brand using a query?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COHa! `[Customer Brand Purchases]` is actually a query, that's why it has a terrible but descriptive name. I'm querying a query like it was a table (that may be why my original attempt is timing out). I tried this but it didn't work unfortunately, it said there was an error in the FROM clause 3 times and produced an empty result.
      singulars
      1. PO
      1. USMrMisterMan
    2. CO+1 - I'm not up on the MSAccess dialect, but that looks like what I'd try on DB2, and should be largely portable elsewhere regardless. If the combination [`email`, `brand_id`] isn't unique (given that this is a summary table), they have an even larger problem, so that's not likely an issue here. And we can only hope that this knowledge won't be used for _too_ nefarious a purpose by the OP (or his marketing department).
      singulars
      1. PO
      1. USClockwork-Muse
    3. COAccess will accept a query name which doesn't include spaces: Customer_Brand_Purchases. And without embedded spaces, you don't need to bracket the name when you reference it in other SQL statements.
      singulars
      1. PO
      1. USHansUp
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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