Categorizing Sales by Team

Question

The question is: "In one record for each squad, show the team, its US (area = ‘-US’) sales for 2018 and its UK (area = ‘UK’) sales for 2018. "

Would this not work?

SELECT squad, SUM(CASE WHEN area = “US” THEN sales_2018 ELSE 0 END) AS “US”, SUM(CASE WHEN area = “UK” then sales_2018 ELSE 0 END) AS “UK”
FROM squad A
Inner join Sales b on a.account = b.account
GROUP BY squad

Tables involved:

Answer 1

From what i understand you can use pivot to achieve your result.

select squad,[US],[UK] from(
select sq.squad,sq.Area,sa.sales_2018 from Sales sa inner join Squad sq on sa.account=sq.account
) tbl
pivot(
sum(sales_2018) for Area in ([US],[UK])
) pvtTable

The result of inner query returns

squad	Area	sales_2018
IT	US	1300.00
IT	UK	3300.00
Finance	US	3400.00
Finance	UK	2500.00

After applying Pivot

squad	US	UK
IT	3400.00	2500.00
Finance	1300.00	3300.00