grouping and summing

[sandease]

My problem is that my transaction table can contain 1 or more lines for the same product for a customer. I need to make a subquery that will, by customer, sum the quantity for like products and then extract the all of the information for the product with the largest quantity so it can be used in the query above the subquery. The subquery below gives me one line per product(symbol). How do I query this to get ALL of the fields for the first line (largest quantity) of each customer group. Each customer group will contain many different products.


SELECT ACCOUNT_NB, SYMBOL, SUM(QUANTITY) QTY, PRICE, PRICE * SUM(QUANTITY) VALUE
FROM STG_SIS_PRICE
GROUP BY ACCOUNT_NB, SYMBOL, TR_DATE, PRICE
ORDER BY ACCOUNT_NB, SUM(QUANTITY) DESC

 

[jad]

so ... you want:
ACCOUNT_NB, SYMBOL, SUM(QUANTITY) QTY, PRICE, PRICE * SUM(QUANTITY) VALUE
out ...
where account_nb is the customer group?
and symbol is the product?

and you want the max grossing (sum of quantity) symbol for each account_nb?

does:

select ACCOUNT_NB, SYMBOL, QTY, PRICE, VALUE
  from (select ACCOUNT_NB, SYMBOL, SUM(QUANTITY) QTY, PRICE, PRICE * SUM(QUANTITY) VALUE
            from stg_sis_price
            group by ACCOUNT_NB, SYMBOL, TR_DATE, PRICE) accounts
  where accounts.qty = (
        select max(sum(quantity))
            from stg_sis_price ssp
            where accounts.account_nb = ssp.account_nb
            group by ssp.symbol)
  ORDER BY ACCOUNT_NB

work?

 

[sandease]

jad....close, but not quite there. If the top quantity = 200 and the customer has more than one product that totals to 200, then all products with total quantity of 200 are returned.

 

[jad]

then which one did you want?

your original query could easily have produced any order after account_nb and sum(quantity).

if you need any different, you'll have to specify the order more, and then i'll work it out/give you pl/sql for the job.

 

[sandease]

jad,
You're solution highlights a problem I didn't contemplate...What happens when more than 1 product sums to maximum quantity. If I wanted to include tr_date in the selection criteria and use the most recent tr_date to select between the multiple Max quantities, where would I insert it?
Thank you for your help. I sincerely appreciate it.

 

[jad]

hmm ... harder ... i can't test this i don't have anything like it

does:

select accounts.ACCOUNT_NB, accounts.SYMBOL, ik.QTY, accounts.PRICE,
       accounts.PRICE ik.qty VALUE
    from (select ACCOUNT_NB, SYMBOL, SUM(QUANTITY) QTY, tr_date
            from stg_sis_price
            group by ACCOUNT_NB, SYMBOL, TR_DATE, PRICE) accounts,
         (select ACCOUNT_NB, QTY, max(tr_date) max_tr_date
              from (select ACCOUNT_NB, SYMBOL, SUM(QUANTITY) QTY, tr_date
                        from stg_sis_price
                        group by ACCOUNT_NB, SYMBOL, TR_DATE, PRICE) accounts
              where accounts.qty = (select max(sum(quantity))
                                        from stg_sis_price ssp
                                        where accounts.account_nb = ssp.account_nb
                                        group by ssp.symbol)
              group by account_nb, qty) ik
  where accounts.account_nb = ik.account_nb
    and accounts.qty = ik.qty
    and accounts.tr_date = ik.max_tr_date
  ORDER BY ACCOUNT_NB;

work?

 

[jad]

umm ... you'll need to change the order by clause to include 'accounts.' ... and the * appears to be missing on the 'PRICE * QTY' bit.

 

[sandease]

jad, While you were working on the inclusion of the date problem, I took your first solution and worked with it. This is what I came up with. I ordered the returned items by descending date and then numbered the rows. Out of the numbered rows, I select row #1. This will give me one line per account, selecting the most recent Transaction when max quantity is equal for more than one product. Does it look good to you?

SELECT ACCOUNT_NB, TOTQTY, SYM, PRICE, TR_DATE, PRICE * SUM(QUANTITY) VALUE, rn
FROM
(SELECT ACCOUNT_NB, TOTQTY, SYM, PRICE, TR_DATE, ROW_NUMBER () OVER (PARTITION BY ACCOUNT_NB ORDER BY ACCOUNT_NB) AS RN
FROM
(SELECT DISTINCT ACCOUNT_NB, SYMBOL SYM, PRICE, TR_DATE,
SUM(QUANTITY) OVER (PARTITION BY ACCOUNT_NB, SYMBOL, TR_DATE) TOTQTY
FROM STG_SIS_PRICE
ORDER BY ACCOUNT_NB, TOTQTY DESC, TR_DATE DESC) A )B
WHERE RN = 1

 

[jad]

if it works, then it's fine by me )