samedi 20 juin 2020

Check if subquery result exists and then subtract it

I have a big query where I have to subtract subquery value. I tried JOINS, but they are not working as there is multiple grouping that results in incorrect mathematical equation results. The query:

SELECT 
o.order_id, 
sa.shipping_name AS user, 
ROUND(SUM(op.price * op.amount) + o.shipping_price - IF(EXISTS(SELECT SUM(od.discount) FROM order_discounts od WHERE od.order_id=o.order_id GROUP BY od.order_id), (SELECT SUM(od.discount) FROM order_discounts od WHERE od.order_id=o.order_id GROUP BY od.order_id), 0), 2) 

AS order_total FROM 
( orders o JOIN order_products op ON op.order_id=o.order_id ) 
LEFT OUTER JOIN shipping_addresses sa ON o.shipping_address_id = sa.shipping_address_id 
GROUP BY o.order_id ORDER BY o.order_date DESC, o.user_id DESC

As you can see I have to run a query to check if there is a discount and then run it again to subtract. So my question is how can I get the "SELECT SUM(od.discount)..." part only once?

The structure of tables:

CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `order_date` datetime NOT NULL,
  `shipping_address_id` int(11) NOT NULL,
  `shipping_price` float NOT NULL,
  `shipping_option_id` int(11) NOT NULL,
  `order_currency` varchar(5) CHARACTER SET utf8mb4 NOT NULL,
  `billing_address_id` int(11) NOT NULL,
  `status` enum('unpaid','paid','shipped') CHARACTER SET utf8mb4 NOT NULL,
  `admin_id` int(11) NOT NULL,
  `descr` text CHARACTER SET utf8mb4 NOT NULL,
  `payment_type` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  `invoice_url` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  `amount_paid` double NOT NULL,
  `stripe_src_id` varchar(50) NOT NULL,
  `ipn_message` text NOT NULL,
  `stripe_client_secret` varchar(255) NOT NULL,
  `order_total` double NOT NULL,
  `paid_via` enum('paypal','stripe','bancontact') NOT NULL,
  `order_notes` text CHARACTER SET utf8mb4 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `order_discounts`
--

CREATE TABLE `order_discounts` (
  `order_id` int(11) NOT NULL,
  `discount_id` int(11) NOT NULL,
  `discount` float NOT NULL,
  `currency` varchar(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `order_products`
--

CREATE TABLE `order_products` (
  `op_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `amount` int(7) NOT NULL,
  `price` double NOT NULL,
  `currency` varchar(5) CHARACTER SET utf8mb4 NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `shipping_addresses`
--

CREATE TABLE `shipping_addresses` (
  `shipping_address_id` int(11) NOT NULL,
  `shipping_name` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Aucun commentaire:

Enregistrer un commentaire