Reputation: 33
I have the following query that gives me the data that I want, however, I need the total sum of the Cash, Credit and Check columns inside my CASE statements. How can I achieve this? I'd like to use a procedure for this if possible.
Also, to me, this query doesn't seem at all that efficient. Can anyone improve upon this? It seems to me that I should be able to set variables and then use them inside my CASE statements but not sure how it's done.
SELECT
t1.order_id,
t3.price * SUM( t1.quantity ) AS subtotal,
( SUM( t1.discount ) + t3.discount ) / 100 AS disc,
t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS tax,
t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS total,
CASE t2.payment_type WHEN 'Cash' THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 ELSE 0.00 END AS cash,
CASE t2.payment_type WHEN 'Card' THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 ELSE 0.00 END AS card,
CASE t2.payment_type WHEN 'Check' THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 ELSE 0.00 END AS check
FROM pos_item_order AS t1
Join pos_order AS t2 ON t2.order_id = t1.order_id
Join inv_item AS t3 ON t3.item_id = t1.item_id
GROUP BY t1.order_id, t1.item_id
EDIT: When I say that I "need the total sum of the Cash, Credit and Check columns", I mean the respective totals per column.
Upvotes: 2
Views: 1575
Reputation: 755094
That is a gruesome query, but a simple-minded extension to what you already have gives you what I think you are asking for:
SELECT t1.order_id,
t3.price * SUM( t1.quantity ) AS subtotal,
( SUM( t1.discount ) + t3.discount ) / 100 AS disc,
t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS tax,
t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS total,
CASE t2.payment_type WHEN 'Cash'
THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100
ELSE 0.00 END AS cash,
CASE t2.payment_type WHEN 'Card'
THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100
ELSE 0.00 END AS card,
CASE t2.payment_type WHEN 'Check'
THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100
ELSE 0.00 END AS check,
CASE WHEN t2.payment_type IN ('Cash', 'Card', 'Check')
THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 ELSE 0.00 END AS cash_card_check
FROM pos_item_order AS t1
JOIN pos_order AS t2 ON t2.order_id = t1.order_id
JOIN inv_item AS t3 ON t3.item_id = t1.item_id
GROUP BY t1.order_id, t1.item_i
The IN(...)
notation might not work; if not, you can write out a three-way OR
condition instead. However, I can't help but think there has to be a better way of structuring your query.
This query gives you the basic details, including the payment type. Save it into a temporary table, or use a named subexpression in a WITH clause if your DBMS supports that.
SELECT t1.order_id,
t2.payment_type,
t3.price * SUM( t1.quantity ) AS subtotal,
( SUM( t1.discount ) + t3.discount ) / 100 AS disc,
t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS tax,
t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS total,
t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS payment
FROM pos_item_order AS t1
JOIN pos_order AS t2 ON t2.order_id = t1.order_id
JOIN inv_item AS t3 ON t3.item_id = t1.item_id
GROUP BY t1.order_id, t1.item_i, t2.payment_type
You can then aggregate the cards, the checks and the cash separately and jointly.
Upvotes: 1
Reputation: 101614
Unfortunately you can't use derived columns in another expression, but you may be able to use a subquery to achieve what you'd like.
Getting past my bedtime, I'll give it a go tomorrow if someone else doesn't already beat me to it, but thought I'd give you the direction to go in in the mean time.
Upvotes: 0