jeudi 3 mars 2016

select and sum 3 highest values from id sqlite

I would like to ask how to select 3 highest values and sum it in sqlite.

My table looks like this:

   ID           SKU         Price         
   -----        -----       -----
   John         Product1    100
   John         Product2    10
   Steven       Product3    20
   Rachel       Product4    100
   Jack         Product5    10
   John         Product6    10
   John         Product7    10

Easy solution would be to: select Id, group_concat(SKU), sum(price) from my_table group by id;

This would give me overall picture, but what if I want to include only three highest values from each id and of course sum those three values? So the end result would look something like this:

       ID           SKU                              Price         
       -----        -----                            -----
       John         Product1,Product2,Product6       120
       Steven       Product3                         20
       Rachel       Product4                         100
       Jack         Product5                         10

Aucun commentaire:

Enregistrer un commentaire