SQL Server Performance

Report a number as percentage of whole

Discussion in 'General Developer Questions' started by mikel4538, Jan 15, 2009.

  1. mikel4538 New Member

    I need to create an SQL that will show amount of sales for specific agencies and what percentage of all sales this number represents
    Create table tmp(agency char(16),pax int,sales money)
    insert into tmp values('ABC',10,1100.00)
    insert into tmp values('DEF',1,100.00)
    insert into tmp values('GHI',6,1800.00)
    insert into tmp values('JKL',8,1000.00)
    I need to show that ABC and DEF have 11 pax, 1200.00 in sales and toal pax is 25 and total sales is 7000.00
    Is this possible in 1 SQL statement?
  2. Adriaan New Member

    If you make your PAX column a DECIMAL(5,2) or similar ---
    SELECT my_agencies.pax_amount, (my_agencies.pax_amount/ all_agencies.pax_total) * 100 as pax_percentage,
    my_agencies.sales_amount, (my_agencies.sales_amount / all_agencies.sales_total) * 100 as sales_percentage
    FROM (select sum(pax) as pax_total, sum(sales) as sales_total from #tmp) AS all_agencies,
    (select sum(pax) as pax_amount, sum(sales) as sales_amount from #tmp where agency in ('ABC', 'DEF')) AS my_agencies
  3. mikel4538 New Member

    Thanks, that works perfectly

Share This Page