CODEWITHSUNDEEP
phpmysql
Neros
Neros

Reputation: 1129

SQL most popular

I have a mysql table with items in relation to their order.

CREATE DATABASE IF NOT EXISTS `sqltest`;
USE `sqltest`;

DROP TABLE IF EXISTS `testdata`;
CREATE TABLE `testdata` (
  `orderID` varchar(10) DEFAULT NULL,
  `itemID` varchar(10) DEFAULT NULL,
  `qtyOrdered` int(10) DEFAULT NULL,
  `sellingPrice` decimal(10,2) DEFAULT NULL
)

INSERT INTO `testdata`(`orderID`,`itemID`,`qtyOrdered`,`sellingPrice`) 
values ('1','a',1,'7.00'),('1','b',2,'8.00'),('1','c',3,'3.00'),('2','a',1,'7.00'),('2','c',4,'3.00');

Intended Result:

A = (1+1)2

B = 2

C = (2+4)6 <- most popular

How do I add up all the qty's for each item and result the highest one?

It should be fairly strait forward but I'm new to SQL and I can't work this one out :S

Solution needs to be mysql and or php.

I guess there needs to be some sort of temporary tally variable for each item ID, but that seems like it could get messy with too many items.

ANSWER:

(thanks nuqqsa)

SELECT itemID, SUM(qtyOrdered) AS total FROM testdata GROUP BY itemID ORDER BY total DESC LIMIT 1;

Upvotes: 5

Views: 788

Answers (5)

powtac
powtac

Reputation: 41040

SELECT SUM( qtyOrdered ) AS sum_ordered, itemID
FROM testdata
GROUP BY itemID
ORDER BY sum_ordered

Upvotes: 0

Amy B
Amy B

Reputation: 17977

SELECT      *
FROM        testdata
ORDER BY    SUM(gtyOrdered) DESC
GROUP BY    itemID

Upvotes: 0

oezi
oezi

Reputation: 51797

SELECT itemID, SUM(qtyOrdered) as blah FROM sqltest GROUP BY itemID ORDER BY blah DESC should do it

Upvotes: 0

nuqqsa
nuqqsa

Reputation: 4521

How about this:

SELECT itemID, SUM(qtyOrdered) AS total FROM testdata GROUP BY itemID ORDER BY total DESC;

Upvotes: 6

Grumpy
Grumpy

Reputation: 2243

select count(qtyOrdered), qtyOrdered from testdata group by qtyOrdered

Upvotes: -4

Related Questions