MySQL result select SUM from the database

I’ve the following tables and I try to select from table1, table2 and table3 and sum them in total:

CREATE TABLE `tbl_cars` (
  `carID` int(11) NOT NULL,
  `car` varchar(255) NOT NULL,
  `product1` int(11) NOT NULL,
  `product1_amount` int(11) NOT NULL,
  `product2` int(11) NOT NULL,
  `product2_amount` int(11) NOT NULL,
  `product3` int(11) NOT NULL,
  `product3_amount` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `tbl_cars` (`carID`, `car`, `product1`, `product1_amount`, `product2`, `product2_amount`, `product3`, `product3_amount`) VALUES
(1, 'Kamacho 180 PS', 1, 10, 0, 0, 3, 10),
(2, 'Komoda 123 PS', 1, 20, 2, 20, 0, 0);

CREATE TABLE `tbl_orders` (
  `orderID` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `ordered` varchar(255) NOT NULL,
  `pending` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `money` varchar(255) NOT NULL,
  `number` varchar(255) NOT NULL,
  `idcard` bigint(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `account` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `tbl_orders` (`orderID`, `name`, `ordered`, `pending`, `status`, `money`, `number`, `idcard`, `date`, `account`) VALUES
(1, 'Some_Name', '1', 1, 0, '1000', '1822-25136145', 21474836471, '2020-11-26 05:44:00', '');

CREATE TABLE `tbl_prices` (
  `priceID` int(11) NOT NULL,
  `product` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Daten für Tabelle `tbl_prices`
--

INSERT INTO `tbl_prices` (`priceID`, `product`, `price`) VALUES
(1, 'Rolle Stoff', 5.00),
(2, 'Rolle Pappe', 10.00),
(3, 'Eiesenbarren', 15.00),
(4, 'Rolle Stoff123', 20.00),
(5, 'Rolle Pappe123', 25.00),
(6, 'Eiesenbarren123', 30.00);

What i’ve done is currently this

SELECT product1_amount*price as total FROM tbl_prices, tbl_orders, tbl_cars WHERE orderID = '1' AND ordered = carID AND priceID = product1
UNION
SELECT product2_amount*price as total FROM tbl_prices, tbl_orders, tbl_cars WHERE orderID = '1' AND ordered = carID AND priceID = product2
UNION
SELECT product3_amount*price as total FROM tbl_prices, tbl_orders, tbl_cars WHERE orderID = '1' AND ordered = carID AND priceID = product3

result

total
50
150

So far so good, but i’m looking for this result

total
200

I tried with

SELECT SUM(total) total FROM (
SELECT product1_amount*price as total FROM tbl_prices, tbl_orders, tbl_cars WHERE orderID = '1' AND ordered = carID AND priceID = product1
UNION
SELECT product2_amount*price as total FROM tbl_prices, tbl_orders, tbl_cars WHERE orderID = '1' AND ordered = carID AND priceID = product2
UNION
SELECT product3_amount*price as total FROM tbl_prices, tbl_orders, tbl_cars WHERE orderID = '1' AND ordered = carID AND priceID = product3
)t
GROUP BY total

but this result is still the same result as the first one

total
50
150

Searched now about a hour on google to find a solution and this was my best try to get any result at all what i’m looking for. If there is also a better solution i’d really like to learn more about it. Anyway for any fix for my question would be amazing.

EDIT:

sample data http://sqlfiddle.com/#!9/c063a7/4

Answer

If you want to find the total the u have to sum all the individual totals not union them

 SELECT
  ISNULL(( SELECT product1_amount*price as total FROM tbl_prices, tbl_orders, tbl_cars WHERE orderID = '1' AND ordered = carID AND priceID = product1 ),0)
  +
  ISNULL(( SELECT product2_amount*price as total FROM tbl_prices, tbl_orders, tbl_cars WHERE orderID = '1' AND ordered = carID AND priceID = product2 ),0)
  +
  ISNULL(( SELECT product3_amount*price as total FROM tbl_prices, tbl_orders, tbl_cars HERE orderID = '1' AND ordered = carID AND priceID = product3),0)
 as total;

Advertisement

Answer