Aggregate subset of data in separate columns without subquery

Question

I have a table, let's call it transactions, like this

transaction_id	account	location	amount
1	cogs	a	100
2	cogs	a	150
3	cogs	b	200
4	cogs	b	100
5	sales	a	225
6	sales	a	75
5	sales	b	250
6	sales	b	100

I would like to sum amount, effectively grouped by location and account, with a separate column for each account like so

location	cogs_total	sales_total
a	250	300
b	300	350

Typically, I would achieve this by JOINing the table with itself like this

SELECT cogs.location,
    SUM(cogs.amount) AS 'cogs_total',
    sales.sales_total
FROM transactions cogs
LEFT JOIN (
    SELECT location,
        SUM(amount) AS 'sales_total'
    FROM transactions
    WHERE account = 'sales'
    GROUP BY location
) sales ON sales.location = cogs.location
WHERE cogs.account = 'cogs'
GROUP BY location;

However, I'm working with an API that only provides a restricted SQL syntax which does not allow for JOINing on a subquery like this. Is there a way around this limitation that would allow me to achieve the same results by different means?

Background info

The API that I'm working with is the Netsuite SuiteTalk REST API and the queries use SuiteQL which is a subset of Oracle SQL.

The actual tables I'm working with are similar to the example above. The goal is to be able to return a gross margin for accounting segments. The actual data I'm working with is similar to this

transaction_id	account	location	department	amount
1	cogs	a	camping	100
2	cogs	a	spatula	150
3	cogs	b	camping	200
4	cogs	b	spatula	100
5	sales	a	camping	150
6	sales	a	spatula	200
5	sales	b	camping	250
6	sales	b	spatula	150

and the results I'm looking for would be more along the lines of

location	department	cogs_total	sales_total	gross_margin
a	camping	100	150	5%
a	spatula	150	200	25%
b	camping	200	250	20%
b	spatula	100	150	33%

Gross margin percentage = ((Revenue - COGS) / Revenue) * 100

I wanted to mention these extra details in case the addition of extra columns to group by and the additional column for calculating the gross margin would affect the solutions.

Answer 1

You need conditional aggregation as follows:

SELECT location,
       sum(case when account = 'cogs' then amount end) as cogs_total,
       sum(case when account = 'sales' then amount end) as sales_total
  FROM transactions
GROUP BY location