BenRichi_ BenRichi_ - 7 months ago 21
SQL Question

Query for best selling format not working correctly

I am trying to ask my database the question "What stock format is the best selling?". Below is some sample data and the code that makes up the database itself. I am using phpMyadmin in order to create the database, so this is a data dump from Myadmin.

CREATE TABLE IF NOT EXISTS `tbl_sale` (
`id_sale` int(11) NOT NULL AUTO_INCREMENT,
`id_stock` int(11) NOT NULL,
`id_customer` int(11) NOT NULL,
`id_employee` int(11) NOT NULL,
`id_invoice` int(11) NOT NULL,
PRIMARY KEY (`id_sale`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ;

INSERT INTO `tbl_sale` (`id_sale`, `id_stock`,`id_customer`,`id_employee`, `id_invoice`) VALUES
(2, 2, 1, 1, 1),
(3, 1, 1, 1, 1),
(4, 3, 1, 1, 1),
(5, 4, 2, 2, 2),
(6, 5, 2, 2, 2),
(7, 6, 3, 3, 3),
(8, 7, 4, 4, 4),
(9, 8, 4, 5, 4),
(10, 9, 4, 5, 4),
(11, 10, 4, 6, 4),
(12, 11, 5, 7, 5),
(13, 12, 5, 7, 5),
(14, 13, 6, 8, 6),
(15, 14, 6, 8, 6),
(16, 15, 6, 8, 6),
(17, 16, 7, 9, 7),
(18, 17, 8, 10, 8),
(19, 18, 8, 10, 8),
(20, 19, 9, 1, 9),
(21, 20, 9, 1, 9),
(22, 21, 9, 1, 9),
(23, 22, 9, 1, 9),
(24, 1, 10, 2, 10),
(25, 2, 11, 3, 11),
(26, 3, 12, 4, 12),
(27, 4, 12, 4, 12),
(28, 5, 13, 5, 13),
(29, 6, 14, 6, 14),
(30, 7, 15, 7, 15),
(31, 8, 16, 7, 16);

CREATE TABLE IF NOT EXISTS `tbl_stock` (
`id_stock` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL,
`quantity` int(11) NOT NULL,
`price_cost` decimal(10,2) NOT NULL,
`price_sale` decimal(10,2) NOT NULL,
`weight` int(25) NOT NULL,
`id_format` int(11) NOT NULL,
`id_genre` int(11) NOT NULL,
PRIMARY KEY (`id_stock`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=23 ;

INSERT INTO `tbl_stock` (`id_stock`, `name`, `quantity`, `price_cost`,`price_sale`, `weight`, `id_format`, `id_genre`) VALUES
(1, 'Star Wars: The Force Awak', 10, 2.39, 10.00, 16, 1, 1),
(2, 'The Hunger Games: Mocking', 15, 2.28, 11.00, 16, 3, 2),
(3, 'Deadpool', 20, 4.00, 15.99, 16, 1, 14),
(4, 'The Jungle Book', 10, 1.78, 13.99, 16, 1, 1),
(5, 'Captain America: Civil Wa', 13, 2.59, 12.99, 16, 3, 1),
(6, 'Batman v Superman: Dawn o', 15, 2.99, 14.99, 16, 3, 14),
(7, 'The Huntsman: Winter''s Wa', 14, 2.99, 13.00, 16, 1, 5),
(8, 'The Magnificent Seven', 10, 1.36, 11.99, 16, 1, 16),
(9, 'Hardcore Henry', 5, 1.24, 10.99, 16, 3, 18),
(10, 'Doctor Strange', 8, 2.54, 14.99, 16, 1, 2),
(11, 'Power Rangers ', 9, 0.97, 13.99, 16, 3, 14),
(12, '10 Cloverfield Lane', 5, 1.34, 12.99, 16, 3, 10),
(13, 'Star Wars Battlefront', 10, 8.49, 29.50, 100, 6, 18),
(14, 'Quantum Break', 20, 15.78, 38.61, 100, 6, 18),
(15, 'Call of Duty: Black Ops I', 12, 9.89, 30.00, 100, 5, 18),
(16, 'One Voice', 5, 1.52, 9.99, 58, 2, 21),
(17, 'NOW That''s What I Call Mu', 3, 1.29, 13.00, 55, 2, 21),
(18, 'Ratchet and Clank', 10, 7.89, 28.00, 100, 6, 17),
(19, 'Uncharted 4: A Theif''s En', 10, 7.89, 44.00, 100, 6, 18),
(20, 'Dark Sould III', 10, 9.99, 38.99, 100, 5, 19),
(21, 'Grand Theft Auto V', 12, 11.99, 37.49, 100, 5, 20),
(22, 'FIFA 16', 7, 5.89, 34.95, 100, 6, 21);

CREATE TABLE IF NOT EXISTS `tbl_stock_format` (
`id_format` int(11) NOT NULL AUTO_INCREMENT,
`format` varchar(25) NOT NULL,
PRIMARY KEY (`id_format`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

INSERT INTO `tbl_stock_format` (`id_format`, `format`) VALUES
(1, 'DVD'),
(2, 'CD'),
(3, 'Blu-Ray'),
(4, 'Vinyl'),
(5, 'Playstation 4'),
(6, 'Xbox One');


The query I am trying to use is this:

SELECT id_format, MAX(MAX_COUNT)
FROM(
SELECT id_format, COUNT(id_format) AS MAX_COUNT
SELECT*FROM tbl_sale, tbl_stock_format
WHERE tbl_sale.id_format = tbl_stock_format.id_format
GROUP BY id_format) AS COUNT


However U get this error:


1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT*FROM tbl_sale, tbl_stock_format WHERE tbl_sale.id_format = tbl_stock_form' at line 4


Can anyone tell me what I am doing wrong please?

Answer

This query is full of nonsense. You have table columns being selected that don't belong to the table: tbl_sale.id_format, you have multiple select queries with no structure.

Try this query. If this is not outputting the correct results it's a much better start than what you have shown.

SELECT tbl_stock_format.id_format, tbl_stock_format.format, COUNT(tbl_stock_format.id_format) AS count
FROM tbl_stock_format 
INNER JOIN tbl_stock
ON tbl_stock.id_format = tbl_stock_format.id_format
INNER JOIN tbl_sale
ON tbl_sale.id_stock = tbl_stock.id_stock
GROUP BY tbl_stock_format.id_format
ORDER BY count DESC
Comments