I'm trying to get of all sizes, and making result as one inside GROUP_CONCAT().
GROUP_CONCAT() then, we can use SUM()NULL or other size of product_size.Size_Name THEN do not get a SUM() of this.By the way this is my query:
SELECT
product_table.Product_Name,
SUM(product_quantity.Quantity) AS 'QUANTITY',
GROUP_CONCAT(
product_quantity.Quantity,
' box/es ',
product_size.Size_Name,
' size' SEPARATOR '
'
) AS 'SIZE'
FROM
product_quantity
RIGHT JOIN
product_table
ON
product_quantity.Product_ID = product_table.Product_ID
LEFT JOIN
product_size
ON
product_quantity.Size_ID = product_size.size_ID
GROUP BY
product_table.Product_Name ASC
and so this is the result :
PRODUCT QUANTITY SIZE
Hawaiian Pizza 11 2 - box/es Large size
3 - box/es Large size
1 - box/es Small size
5 - box/es Large size
And it is the correct answer on what I trying to get right now :
PRODUCT QUANTITY SIZE
Hawaiian Pizza 11 10 - box/es Large size
1 - box/es Small size
But how ? Any Idea ???
By the way this is my table structure:
For,
table product:
CREATE TABLE `product_table` (
`Product_ID` int(40) NOT NULL,
`Product_Name` varchar(400) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `product_table` (`Product_ID`, `Product_Name`) VALUES
(1001, 'Hawaiian Pizza'),
(1002, 'Chicken Alfredo'),
(1003, 'Chicken Quesadillas'),
(1004, 'Mexican Pizza'),
(1006, 'Beef Pepperoni Pizza');
ALTER TABLE `product_table`
ADD PRIMARY KEY (`Product_ID`),
ADD KEY `Product_ID` (`Product_ID`);
ALTER TABLE `product_table`
MODIFY `Product_ID` int(40) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=1007;
table product_size:
CREATE TABLE `product_size` (
`Size_ID` int(100) NOT NULL,
`Size_Name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `product_size` (`Size_ID`, `Size_Name`) VALUES
(1, 'Small'),
(2, 'Medium'),
(3, 'Extra Large'),
(4, 'Large');
ALTER TABLE `product_size`
ADD PRIMARY KEY (`Size_ID`);
ALTER TABLE `product_size`
MODIFY `Size_ID` int(100) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
table product_quantity:
CREATE TABLE `product_quantity` (
`Quantity_ID` int(100) NOT NULL,
`Quantity` int(100) NOT NULL,
`Product_ID` int(100) NOT NULL,
`Size_ID` int(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `product_quantity` (`Quantity_ID`, `Quantity`,
`Product_ID`, `Size_ID`) VALUES
(18, 2, 1002, 3),
(19, 1, 1001, 2),
(20, 2, 1003, 1),
(21, 3, 1002, 2),
(22, 1, 1002, 3),
(23, 2, 1003, 3),
(24, 3, 1004, 4),
(25, 1, 1003, 3),
(26, 3, 1006, 3),
(27, 4, 1002, 3),
(28, 2, 1004, 4),
(29, 4, 1002, 3),
(30, 2, 1002, 3),
(31, 2, 1002, 2),
(32, 3, 1003, 3),
(33, 23, 1002, 4);
ALTER TABLE `product_quantity`
ADD PRIMARY KEY (`Quantity_ID`),
ADD KEY `Product_ID` (`Product_ID`),
ADD KEY `Size_ID` (`Size_ID`);
ALTER TABLE `product_quantity`
MODIFY `Quantity_ID` int(100) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=34;
ALTER TABLE `product_quantity`
ADD CONSTRAINT `product_quantity_ibfk_1` FOREIGN KEY
(`Product_ID`) REFERENCES `product_table` (`Product_ID`)
ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `product_quantity_ibfk_2` FOREIGN KEY (`Size_ID`)
REFERENCES `product_size` (`Size_ID`) ON DELETE CASCADE ON
UPDATE CASCADE;
I dont think you need group_concat. a normal group by should do it.
SELECT product_table.product_name,
Sum(product_quantity.quantity),
CONCAT(' box/es ',
product_size.size_name,
' size') AS 'SIZE'
FROM product_quantity
RIGHT JOIN product_table
ON product_quantity.product_id = product_table.product_id
LEFT JOIN product_size
ON product_quantity.size_id = product_size.size_id
GROUP BY product_table.product_name ASC,
product_size.size_name
Provide source data and a more precise data output and I can create a sample demo.