CODEWITHSUNDEEP
mysqlsubquerysumaggregatemax
ArgGrr
ArgGrr

Reputation: 23

Aggregate function not working as expected with subquery

Having some fun with MySQL by asking it difficult questions.

Essentially i have a table full of transactions, and from that i want to determine out of all the available products (productid), who (userid) has bought the most of each? The type in the where clause refers to transaction type, 1 being a purchase.

I have a subquery that on its own returns a list of the summed products bought for each person, and it works well by itself. From this i am trying to then pick the max of the summed quantities and group by product, which is a pretty straight forward aggregate. Unfortunately it's giving me funny results! The userid does not correspond correctly to the reported max productid sales.

select 
    `userid`, `productid`, max(`sumqty`)
from
    (select 
        `userid`, `productid`, sum(`qty`) as `sumqty`
    from
        `txarchive`
    where
        `type` = 1
    group by `userid`,`productid`) as `t1`
group by `productid`

I have removed all the inner joins to give more verbal results as they don't change the logic of it all.

Here is the structure of tx if you are interested.

id          bigint(20)    #transaction id
UserID      bigint(20)    #user id, links to another table.
ProductID   bigint(20)    #product id, links to another table.
DTG         datetime      #date and time of transaction
Price       decimal(19,4) #price per unit for this transaction
QTY         int(11)       #QTY of products for this transaction
Type        int(11)       #transaction type, from purchase to payment etc.
info        bigint(20)    #information string id, links to another table.

*edit Working final query: (Its biggish)

select 
    `username`, `productname`, max(`sumqty`)
from
    (select 
        concat(`users`.`firstname`, ' ', `users`.`lastname`) as `username`,
            `products`.`name` as `productname`,
            sum(`txarchive`.`qty`) as `sumqty`
    from
        `txarchive`
    inner join `users` ON `txarchive`.`userid` = `users`.`id`
    inner join `products` ON `txarchive`.`productid` = `products`.`id`
    where
        `type` = 1
    group by `productname`,`username`
    order by `productname`,`sumqty` DESC) as `t1`
group by `productname`
order by `sumqty` desc

Upvotes: 1

Views: 613

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Not the best solution (not even guaranteed to work 100% of the times):

select 
    `userid`, `productid`, max(`sumqty`)
from
    ( select 
          `userid`, `productid`, sum(`qty`) as `sumqty`
      from
          `txarchive`
      where
          `type` = 1
      group by 
          `productid`  
        , `userid`
      order by 
          `productid`
        , `sumqty` DESC          
    ) as `t1`
group by
    `productid`

Upvotes: 1

Related Questions