0

For the following table I want to get a table that shows the most sold product in each store Same product can be the most sold in more than 1 store.

This is the query I tried:

SELECT StoreID, ProductID, MAX(Quantity), TotalPrice FROM MyTable GROUP BY StoreID LIMIT 0, 99999;

The answer I get is:

|StoreID|ProductID|MAX(Quantity)|TotalPrice
'1', '1', '566', '288.0000'
'2', '1', '782', '648.0000'
'3', '1', '473', '172.8000'

The problem: I've checked and the max quantities are correct
but for some reason it shows the ID '1' for every product even that it's not the most sold product in any store.

For this table (Called 'MyTable'):

|StoreID|TotalPrice|ProductID|Quantity
'1', '288.0000', '1', '167'
'1', '760.0000', '2', '417'
'1', '400.0000', '3', '70'
'1', '352.0000', '4', '218'
'1', '1105.0000', '5', '117'
'1', '600.0000', '6', '156'
'1', '300.0000', '7', '193'
'1', '960.0000', '8', '84'
'1', '1552.0000', '9', '36'
'1', '558.0000', '10', '115'
'1', '201.6000', '11', '218'
'1', '152.0000', '12', '8'
'1', '9.6000', '13', '187'
'1', '167.4000', '14', '47'
'1', '62.0000', '15', '42'
'1', '417.0000', '16', '451'
'1', '936.0000', '17', '345'
'1', '450.0000', '18', '112'
'1', '7.3000', '19', '68'
'1', '1701.0000', '20', '66'
'1', '80.0000', '21', '337'
'1', '100.8000', '22', '58'
'1', '151.2000', '23', '275'
'1', '43.2000', '24', '431'
'1', '44.8000', '25', '171'
'1', '1245.0000', '26', '297'
'1', '526.5000', '27', '185'
'1', '1528.8000', '28', '127'
'1', '1485.0000', '29', '61'
'1', '124.2000', '30', '158'
'1', '600.0000', '31', '566'
'1', '153.6000', '32', '91'
'1', '120.0000', '33', '294'
'1', '112.0000', '34', '62'
'1', '57.6000', '35', '249'
'1', '456.0000', '36', '200'
'1', '208.0000', '37', '18'
'1', '4216.0000', '38', '86'
'1', '57.6000', '39', '192'
'1', '735.0000', '40', '431'
'1', '123.2000', '41', '220'
'1', '22.4000', '42', '183'
'1', '920.0000', '43', '141'
'1', '325.5000', '44', '281'
'1', '114.0000', '45', '360'
'1', '288.0000', '46', '249'
'1', '418.0000', '47', '156'
'1', '191.2500', '48', '45'
'1', '240.0000', '49', '45'
'1', '406.2500', '50', '25'
'1', '1696.0000', '51', '271'
'1', '112.0000', '52', '146'
'1', '1048.0000', '53', '202'
'1', '59.0000', '54', '180'
'1', '384.0000', '55', '147'
'1', '2128.0000', '56', '428'
'1', '234.0000', '57', '146'
'1', '318.0000', '58', '201'
'1', '3080.0000', '59', '504'
'1', '544.0000', '60', '433'
'1', '364.8000', '61', '221'
'1', '591.0000', '62', '247'
'1', '2808.0000', '63', '187'
'1', '1330.0000', '64', '194'
'1', '336.0000', '65', '250'
'1', '816.0000', '66', '110'
'1', '56.0000', '67', '105'
'1', '30.0000', '68', '245'
'1', '432.0000', '69', '305'
'1', '252.0000', '70', '254'
'1', '344.0000', '71', '326'
'1', '556.0000', '72', '400'
'1', '240.0000', '73', '143'
'1', '400.0000', '74', '70'
'1', '186.0000', '75', '490'
'1', '216.0000', '76', '348'
'1', '104.0000', '77', '255'
'2', '648.0000', '1', '317'
'2', '608.0000', '2', '453'
'2', '160.0000', '3', '128'
'2', '422.4000', '4', '109'
'2', '85.4000', '5', '124'
'2', '150.0000', '6', '77'
'2', '240.0000', '7', '332'
'2', '2240.0000', '8', '222'
'2', '4850.0000', '9', '53'
'2', '396.8000', '10', '435'
'2', '403.2000', '11', '293'
'2', '570.0000', '12', '107'
'2', '96.0000', '13', '477'
'2', '279.0000', '14', '224'
'2', '155.0000', '15', '60'
'2', '139.0000', '16', '294'
'2', '468.0000', '17', '416'
'2', '2000.0000', '18', '218'
'2', '109.5000', '19', '422'
'2', '2592.0000', '20', '219'
'2', '160.0000', '21', '286'
'2', '201.6000', '22', '204'
'2', '180.0000', '23', '187'
'2', '54.0000', '24', '363'
'2', '134.4000', '25', '120'
'2', '249.0000', '26', '164'
'2', '1755.0000', '27', '80'
'2', '1019.2000', '28', '281'
'2', '1980.0000', '29', '458'
'2', '165.6000', '30', '175'
'2', '200.0000', '31', '533'
'2', '1024.0000', '32', '176'
'2', '50.0000', '33', '233'
'2', '156.8000', '34', '158'
'2', '288.0000', '35', '348'
'2', '608.0000', '36', '359'
'2', '468.0000', '37', '18'
'2', '4216.0000', '38', '292'
'2', '604.8000', '39', '317'
'2', '588.0000', '40', '518'
'2', '77.0000', '41', '301'
'2', '112.0000', '42', '223'
'2', '552.0000', '43', '298'
'2', '248.0000', '44', '136'
'2', '380.0000', '45', '40'
'2', '48.0000', '46', '230'
'2', '121.6000', '47', '197'
'2', '153.0000', '48', '15'
'2', '640.0000', '49', '344'
'2', '195.0000', '50', '101'
'2', '1484.0000', '51', '328'
'2', '44.8000', '52', '198'
'2', '393.0000', '53', '320'
'2', '59.0000', '54', '295'
'2', '403.2000', '55', '283'
'2', '121.6000', '56', '486'
'2', '249.6000', '57', '202'
'2', '318.0000', '58', '102'
'2', '660.0000', '59', '630'
'2', '1088.0000', '60', '782'
'2', '2052.0000', '61', '177'
'2', '591.0000', '62', '399'
'2', '280.8000', '63', '223'
'2', '931.0000', '64', '397'
'2', '252.0000', '65', '229'
'2', '408.0000', '66', '95'
'2', '210.0000', '67', '55'
'2', '200.0000', '68', '298'
'2', '518.4000', '69', '232'
'2', '240.0000', '70', '299'
'2', '34.4000', '71', '258'
'2', '667.2000', '72', '200'
'2', '300.0000', '73', '85'
'2', '168.0000', '74', '92'
'2', '37.2000', '75', '542'
'2', '86.4000', '76', '367'
'2', '124.8000', '77', '236'
'3', '172.8000', '1', '344'
'3', '304.0000', '2', '187'
'3', '240.0000', '3', '130'
'3', '1100.0000', '4', '126'
'3', '204.0000', '5', '57'
'3', '120.0000', '6', '68'
'3', '360.0000', '7', '238'
'3', '400.0000', '8', '66'
'3', '582.0000', '9', '6'
'3', '595.2000', '10', '192'
'3', '168.0000', '11', '195'
'3', '364.8000', '12', '229'
'3', '48.0000', '13', '227'
'3', '167.4000', '14', '133'
'3', '248.0000', '15', '20'
'3', '486.5000', '16', '413'
'3', '249.6000', '17', '217'
'3', '600.0000', '18', '209'
'3', '131.4000', '19', '233'
'3', '1814.4000', '20', '28'
'3', '80.0000', '21', '393'
'3', '840.0000', '22', '86'
'3', '288.0000', '23', '118'
'3', '100.8000', '24', '331'
'3', '112.0000', '25', '27'
'3', '597.6000', '26', '292'
'3', '877.5000', '27', '100'
'3', '728.0000', '28', '232'
'3', '990.0000', '29', '227'
'3', '1242.0000', '30', '279'
'3', '150.0000', '31', '298'
'3', '320.0000', '32', '30'
'3', '40.0000', '33', '228'
'3', '224.0000', '34', '286'
'3', '1440.0000', '35', '286'
'3', '380.0000', '36', '246'
'3', '20.8000', '37', '89'
'3', '10540.0000', '38', '245'
'3', '86.4000', '39', '284'
'3', '882.0000', '40', '154'
'3', '192.5000', '41', '460'
'3', '98.0000', '42', '291'
'3', '1472.0000', '43', '141'
'3', '620.0000', '44', '184'
'3', '228.0000', '45', '108'
'3', '144.0000', '46', '69'
'3', '114.0000', '47', '132'
'3', '714.0000', '48', '78'
'3', '560.0000', '49', '131'
'3', '195.0000', '50', '109'
'3', '127.2000', '51', '287'
'3', '112.0000', '52', '156'
'3', '262.0000', '53', '200'
'3', '29.5000', '54', '280'
'3', '2304.0000', '55', '473'
'3', '60.8000', '56', '349'
'3', '312.0000', '57', '86'
'3', '848.0000', '58', '231'
'3', '1320.0000', '59', '362'
'3', '952.0000', '60', '362'
'3', '712.5000', '61', '205'
'3', '472.8000', '62', '437'
'3', '175.5000', '63', '35'
'3', '239.4000', '64', '149'
'3', '672.0000', '65', '266'
'3', '136.0000', '66', '34'
'3', '336.0000', '67', '24'
'3', '100.0000', '68', '256'
'3', '28.8000', '69', '177'
'3', '60.0000', '70', '264'
'3', '516.0000', '71', '473'
'3', '174.0000', '72', '206'
'3', '525.0000', '73', '65'
'3', '288.0000', '74', '135'
'3', '37.2000', '75', '123'
'3', '475.2000', '76', '266'
'3', '156.0000', '77', '300'
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
TheLogicGuy
  • 682
  • 8
  • 19

3 Answers3

1

Try something like below:

SELECT * 
FROM MyTable WHERE (storeID,Quantity) IN 
( SELECT storeID, MAX(Quantity)
  FROM MyTable 
  GROUP BY storeID
)
Polynomial Proton
  • 5,020
  • 20
  • 37
  • It works! thanks Do you know why my solution didnt work? – TheLogicGuy Mar 21 '17 at 18:09
  • If there are multiple products with same quantity then that'll happen. You can group by productId too and select the max if thats not important. Basically you need to find unique identifier to remove the duplicates. – Polynomial Proton Mar 21 '17 at 18:11
  • You updated your comment and i answered previous question. Anyway, in your query it basically selects the `TOP 1` - in other words first available row after grouping. Your query would select MAX only if there are two identicals rows with same value for all the columns and not just Quantity column. – Polynomial Proton Mar 21 '17 at 18:16
  • Refer to Bill's Answer too. he has elaborated the issue quite well. – Polynomial Proton Mar 21 '17 at 18:17
1

The query you wrote is ambiguous. ProductID is not the product that had the max quantity. Think about this query: 

SELECT StoreID, ProductID, MAX(Quantity), MIN(Quantity), TotalPrice 
FROM MyTable 
GROUP BY StoreID LIMIT 0, 99999;

Which ProductID do you expect this query to return? The product that has the max quantity or the product that has the min quantity?

Or this:

SELECT StoreID, ProductID, AVG(Quantity), TotalPrice 
FROM MyTable 
GROUP BY StoreID LIMIT 0, 99999;

Perhaps no product has a quantity that is exactly the average quantity.

This type of SQL query is technically illegal and it's an error in most databases other than MySQL. MySQL allows it, and trusts you to know when you're writing such an ambiguous query.

Tip: Every column of your select-list must be either:

Any other column will not return a reliable value.

You're asking for a query that has been dubbed the problem. This question comes up frequently on Stack Overflow, and it has been answered many times. Here's a couple of my past answers that got a lot of upvotes:

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Why my query is ambigious? Every SupplierID has a ProductID with max sales so I ask for this ProductID – TheLogicGuy Mar 22 '17 at 09:16
  • See my first example above. Why is the ProductID not the product with min sales? – Bill Karwin Mar 22 '17 at 16:02
  • I understand the ambigiousy there but not in my case. – TheLogicGuy Mar 22 '17 at 16:45
  • Nevertheless, SQL does not show the ProductID from the row that had the max sales, simply because you mention max sales in another column of your select-list. You are expecting SQL to do something that it does not do, and the evidence is that you did not get the result you wanted. – Bill Karwin Mar 22 '17 at 17:13
  • And why SupplierID does match the column of max sales? What is the difference between ProductID and SupplierID that makes the former not to match? – TheLogicGuy Mar 22 '17 at 18:10
  • I have no idea what SupplierID is. It doesn't appear in your question or sample queries. – Bill Karwin Mar 22 '17 at 20:33
  • Sorry, StoreID. – TheLogicGuy Mar 22 '17 at 20:57
  • Okay, StoreID matches because you used `GROUP BY StoreID`, therefore the MAX() function will find the max value for Quantity in the group of rows with the same StoreID. That's how `GROUP BY StoreID` works. It makes a group of rows for each distinct value of Store ID, and you can apply aggregate functions to the set of rows in each group, to find max, min, or other results for *all* the rows in the respective group. – Bill Karwin Mar 23 '17 at 21:30
1

It matches because you're grouping by StoreID. MAX is a GROUP BY function, so it returns the MAX(Quantity) for each StoreID.

For ProductID and TotalPrice, the query is just returning the values from the first row for each store, because there's nothing telling it to read the values from the row where Quantity=MAX(Quantity)

TheUknown's query, on the other hand, first finds the MAX(Quantity) for each store, then does a second query to return all the fields from the row where Quantity=MAX(Quantity)

Bob
  • 11
  • 2