Multi-aggregate, Multi-Filter, Single Table SQL

Question

The following simulated table contains order details where cust_nbr represents the order number. I'm trying to find where if an order contains an item_nbr 90000, I need to know if the price for 90000 is greater than the sum of the other items plus tax. I have hundreds of thousands of records in this table. I am using Teradata.

CREATE TABLE Line_Item_Details_Tbl (
    cust_nbr INT,
    trn_dt DATE,
    str_typ VARCHAR(6),
    trn_nbr INT,
    item_nbr INT,
    price DECIMAL(6,2),
    tax DECIMAL(6,2)
);

Sample data:

INSERT INTO Line_Item_Details_Tbl VALUES 
  (5551, '12/22/2011',  'store', 215, 12345, 10.00, 1.25);
INSERT INTO Line_Item_Details_Tbl VALUES 
  (5551, '12/22/2011',  'store', 215, 65715,  6.25, 0.75);
INSERT INTO Line_Item_Details_Tbl VALUES 
  (5551, '12/22/2011',  'store', 215, 90000, 40.00, 0);
INSERT INTO Line_Item_Details_Tbl VALUES 
  (6875, '12/10/2011', 'online', 856, 72345,  8.50, 1.00);
INSERT INTO Line_Item_Details_Tbl VALUES 
  (6875, '12/10/2011', 'online', 856, 65715,  6.25, 0.75);
INSERT INTO Line_Item_Details_Tbl VALUES 
  (3500, '12/12/2011',  'store', 402, 54123, 45.00, 4.00);
INSERT INTO Line_Item_Details_Tbl VALUES 
  (3500, '12/12/2011',  'store', 402, 90000, 20.00, 0);
INSERT INTO Line_Item_Details_Tbl VALUES

The query should do the following:

Select cust_nbr, trn_dt, trn_nbr, sum(price + tax) as purchase
  For a cust_nbr with str_typ  = 'store' AND contains an item_nbr = 90000,
  aggregate price + tax for all items related to cust_nbr except item_nbr 90000

So, preliminary result should be:

cust_nbr  :   trn_dt   : trn_nbr : purchase
5551        12/22/2011   215       $18.25           
3500        12/12/2011   402       $49.00

Then, for each record in the preliminary results, I need to subtract the price of item_nbr 90000 from the purchase and return results only if the purchase is less than the price of item_nbr 90000 as net_cb

So, my ending result should be:

cust_nbr  trn_dt       trn_nbr  net_cb
  5551    12/22/2011   215      ($21.75)

@Donna: proper [sample code](http://sscce.org/) (here, SQL statements) is more useful than any ad hoc schema and sample data format. Please use `CREATE TABLE` and `INSERT ... VALUES` for samples. Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. — outis, Dec 31 '11 at 07:48
... In general, you should respond to requests for clarifications by updating your post, rather than replying with a comment. For one thing, a question should be understandable without reading comments. For another, SO is a QA & site, not a forum, and comments aren't intended (nor are they well suited) for discussions. In this case, you can edit your question and mention the RDBMS in the body, as well as adding a "teradata" tag. — outis, Dec 31 '11 at 07:50
Thank you for your feedback, outis. This was my very first post on this site and I did the best I could with the formatting. You should have seen what I first put out there before edits! It does look much better now. Thanks again! — Donna Panzarotti, Jan 02 '12 at 15:28

Michał Powaga · Answer 1 · 2011-12-31T11:57:54.223

I've tested in on SQL Server 2005 so please do not downvote if it doesn't work at all, just let me know and I will delete my answer :-). I'm just trying to help.

Treat this as your sample data (CTE in SQL Server 2005):

;with ord_det (cust_nbr, trn_dt, str_typ, trn_nbr, item_nbr, price, tax) as (
    select 5551, convert(datetime, '12/22/2011', 101), 'store', 215, 12345, 10.00, 1.25  union all
    select 5551, convert(datetime, '12/22/2011', 101), 'store', 215, 65715, 6.25, 0.75  union all
    select 5551, convert(datetime, '12/22/2011', 101), 'store', 215, 90000, 40.00, null union all
    select 6875, convert(datetime, '12/10/2011', 101), 'online', 856, 72345, 8.50, 1.00  union all
    select 6875, convert(datetime, '12/10/2011', 101), 'online', 856, 65715, 6.25, 0.75  union all
    select 3500, convert(datetime, '12/12/2011', 101), 'store', 402, 54123, 45.00, 4.00  union all
    select 3500, convert(datetime, '12/12/2011', 101), 'store', 402, 90000, 20.00, null
)

Final query (I assumed that your table name is ord_det, if it's not just use proper name):

select t.cust_nbr, t.trn_dt, t.trn_nbr, price - purchase as net_cb from (
    select cust_nbr, trn_dt, trn_nbr, sum(price + coalesce(tax, 0)) as purchase
    from ord_det o
    where item_nbr <> 90000 and str_typ  = 'store'
    group by cust_nbr, trn_dt, trn_nbr
) t
inner join (
    select cust_nbr, trn_dt, trn_nbr, price + coalesce(tax, 0) as price
    from ord_det o
    where item_nbr = 90000 and str_typ  = 'store'
) t1 on t.cust_nbr = t1.cust_nbr
where purchase < price

Result:

cust_nbr    trn_dt                  trn_nbr     net_cb
5551        2011-12-22 00:00:00.000 215         21.75

Thank you for your reply - I think this confused me a little more though I truly do appreciate your taking the time to help me! — Donna Panzarotti, Dec 31 '11 at 23:11

score 1 · Accepted Answer · answered Dec 31 '11 at 09:45

Use a sub-query to identify the transaction you want, then use CASE to determin which records contribute to you aggregates or not.

SELECT
  transactions.cust_nbr,
  transactions.trn_dt,
  transactions.trn_nbr,
  sum(price + tax)                                            AS total,
  sum(CASE WHEN item_nbr = 9000 THEN 0 ELSE price + tax END)  AS total_less_9000
FROM
(
  SELECT
    cust_nbr, trn_dt, trn_nbr
  FROM
    yourTable
  WHERE
    str_typ  = 'store'
    AND item_nbr = 90000
  GROUP BY
    cust_nbr, trn_dt, trn_nbr
)
  AS transactions
INNER JOIN
  yourTable
    ON  transactions.cust_nbr = yourTable.cust_nbr
    AND transactions.trn_dt   = yourTable.trn_dt
    AND transactions.trn_nbr  = yourTable.trn_nbr
GROUP BY
  transactions.cust_nbr, transactions.trn_dt, transactions.trn_nbr

Or simply use the HAVING clause to determine which transactions to include.

SELECT
  cust_nbr,
  trn_dt,
  trn_nbr,
  sum(price + tax)                                            AS total,
  sum(CASE WHEN item_nbr = 9000 THEN 0 ELSE price + tax END)  AS total_less_9000
FROM
  yourTable
GROUP BY
  cust_nbr,
  trn_dt,
  trn_nbr
HAVING
  MAX(CASE WHEN item_nbr = 9000 THEN 1 ELSE 0 END) = 1

Or...

HAVING
  EXISTS (SELECT * FROM yourTable AS lookup
          WHERE cust_nbr = yourTable.cust_nbr
            AND trn_dt   = yourTable.trn_dt
            AND trn_nbr  = yourTable.trn_nbr
            AND item_nbr = 9000
         )

Thank you so much for your replies!! I used the first "HAVING" suggestion and it worked perfectly! Thanks again for helping out a newbie. — Donna Panzarotti, Dec 31 '11 at 23:10

Multi-aggregate, Multi-Filter, Single Table SQL

2 Answers2