SQL most popular
- by Brae
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.
