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?
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