Abdur Rahmaan Abdur Rahmaan - 6 months ago 10
SQL Question

pgsql -Showing top 10 products's sales and other products as 'others' and its sum of sales

I have a table called "products" where it has 100 records with sales details. My requirement is so simple that I was not able to do it.

I need to show the top 10 product names with sales and other product names as "others" and its sales. so totally my o/p will be 11 rows. 11-th row should be others and sum of sales of all remaining products. Can anyone give me the logic?

O/p should be like this,

Name sales
------ -----
1 colgate 9000
2 pepsodent 8000
3 closeup 7000
4 brittal 6000
5 ariies 5000
6 babool 4000
7 imami 3000
8 nepolop 2500
9 lactoteeth 2000
10 menwhite 1500
11 Others 6000 (sum of sales of remaining 90 products)


here is my sql query,

select case when rank<11 then prod_cat else 'Others' END as prod_cat,
total_sales,ID,rank from (select ROW_NUMBER() over (order by (sum(i.grandtotal)) desc) as rank,pc.name as prod_cat,sum(i.grandtotal) as total_sales, pc.m_product_category_id as ID`enter code here`
from adempiere.c_invoice i join adempiere.c_invoiceline il on il.c_invoice_id=i.c_invoice_id join adempiere.m_product p on p.m_product_id=il.m_product_id join adempiere.m_product_category pc on pc.m_product_category_id=p.m_product_category_id
where extract(year from i.dateacct)=extract(year from now())
group by pc.m_product_category_id) innersql
order by total_sales desc


o/p what i got is,

prod_cat total_sales id rank
-------- ----------- --- ----
BSHIRT 4511697.63 460000015 1
BT-SHIRT 2725167.03 460000016 2
SHIRT 2630471.56 1000003 3
BJEAN 1793514.07 460000005 4
JEAN 1115402.90 1000004 5
GT-SHIRT 1079596.33 460000062 6
T SHIRT 446238.60 1000006 7
PANT 405189.00 1000005 8
GDRESS 396789.02 460000059 9
BTROUSER 393739.48 460000017 10
Others 164849.41 1000009 11
Others 156677.00 1000008 12
Others 146678.00 1000007 13

Answer

As @e4c5 suggests, use UNION:

select id, prod_cat, sum(total_sales) as total_sales
with
  totals as (
    select --pc.m_product_category_id as id,
           pc.name as prod_cat,
           sum(i.grandtotal) as total_sales,
           ROW_NUMBER() over (order by sum(i.grandtotal) desc) as rank
    from adempiere.c_invoice i
    join adempiere.c_invoiceline il on (il.c_invoice_id=i.c_invoice_id)
    join adempiere.m_product p on (p.m_product_id=il.m_product_id)
    join adempiere.m_product_category pc on (pc.m_product_category_id=p.m_product_category_id)
    where i.dateacct >= date_trunc('year', now()) and i.dateacct < date_trunc('year', now()) + interval '1' year
    group by pc.m_product_category_id, pc.name
    ),
  rankedothers as (
    select prod_cat, total_sales, rank
    from totals where rank <= 10
    union
    select 'Others', sum(total_sales), 11
    from totals where rank > 10
    )
select prod_cat, total_sales
from ranked_others
order by rank

Also, I recommend using sargable conditions like the one above, which is slightly more complicated than the one you implemented, but generally worth the extra effort.