Help with this query?

[beckwiga]

Hello all! Not sure how I write my query to get the following... If you see the attached screen, you'll see that I occassionally am getting the same UPC twice because my Item Desc is slightly different (typos) so my Group By is generating 2 records. I want to use a Count (which I'm doing in this query) to count the number of stores that have the Item Desc one way, and count the number of stores that have it the other way, and then use the Item Desc that the majority of the Stores are using. So for example, you can see UPC 0000000000161 the Item Desc varies slightly. 51 Stores have it one way, the other 87 stores have it the other way. I want my query to return just the record for 0000000000161 where the Item Desc is "SUNK AW 7UP 20Z 2/3" (where there are more stores with this description).

Does this make sense? How do I structure my query to return just this Item Desc record? I imagine I have to do a subquery of some sort, maybe return TOP 1 ordering by Count(*) DESC or something?

Any help is much appreciated. Thanks!

This query gets the screen shot:

SELECT Product.[UPC Code], Product.[POS Modifier], Product.[Item Code], Product.[Item Desc], Product.[Dept Code], Product.[Cat Code], Product.[Pack Code], Count(*) AS "Count of Stores with Desc"
FROM Product
GROUP BY Product.[UPC Code], Product.[POS Modifier], Product.[Item Code], Product.[Item Desc], Product.[Dept Code], Product.[Cat Code], Product.[Pack Code];

 

[GingerR]

So, I'm lame and would write a few queries.

Save the current query you wrote as "ItemCountDetails".

Write another query based on this one: in the query grid, bring down the UPC Code and the Count of Stores with Desc, do a TOTALS query and make the Count of Stores with Desc = MAX.

SELECT ItemCountDetails.[UPC Code], Max(ItemCountDetails.[Count of Stores with Desc]) AS MaxOfCountOfItemDesc
FROM ItemCountDetails
GROUP BY ItemCountDetails.[UPC Code];

save this query as ProductMaxDescriptions.

This will give you the UPC Codes and the Max Count:

0000000000161 87


Then make a third query, with both previous queries in it; join the UPC Code and the Count fields; this way only the data for the count of 87 (for example) will show up.

Problem is if there is more than one desc with the same count. I guess that could happen. What then?

Probably someone else can write a cool SQL string for all of this, but like I said, I'm lame. Hope this helps.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[beckwiga]

Thanks for the reply, Ginger! I should have mentioned that I am trying to see if this can be done in a single query. Anyone got any other ideas?

 

[lespaul]

what about:

SELECT A.[UPC Code], A.[POS Modifier], A.[Item Code], A.[Item Desc], A.[Dept Code], A.[Cat Code], A.[Pack Code], Max(A.[Count of Stores with Desc]) As BiggestOne FROM (SELECT [UPC Code], [POS Modifier], [Item Code], [Item Desc], [Dept Code], [Cat Code], [Pack Code], Count(*) AS "Count of Stores with Desc"
FROM Product
GROUP BY [UPC Code],[POS Modifier],[Item Code], Product.[Item Desc], [Dept Code], [Cat Code], [Pack Code]) A
GROUP BY A.[UPC Code], A.[POS Modifier], A.[Item Code], A.[Item Desc], A.[Dept Code], A.[Cat Code], A.[Pack Code]

Leslie

Come join me at New Mexico Linux Fest!