2

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.

Jim
  • 33
  • 5

2 Answers2

1

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • thanks for the effort on this. You're right, my query is in fact gruesome looking and I agree, there must be a better way of structuring it. I ran your query and it runs fine, however, you seem to be totaling the individual rows. What I'm looking for is the sum of the Check, Credit and Cash columns. Was that what you were going for? – Jim Jan 26 '11 at 08:43
  • @Jim: the second of the queries is an intermediate result; you would then reprocess it to compute the cash, check, card and grand totals. At least, that was my theory. I'm not quite clear whether a single order has part of it paid in cash, part in check, part with a credit card. You haven't given us enough schema information to be sure. I tend to assume a single order will have a single payment method, but I might be wrong. – Jonathan Leffler Jan 26 '11 at 08:48
  • Ah... I see what you're doing now. Thanks for this effort. Jonathan, if you had to rewrite this query, how might you do it? – Jim Jan 26 '11 at 08:52
0

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.

Community
  • 1
  • 1
Brad Christie
  • 100,477
  • 16
  • 156
  • 200