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

Answer 2

One option is to use PIVOT like here

--  Your first table
WITH    --  S a m p l e    D a t a :
    tbl (ID, ACCOUNT, LOCATION, DEPT, AMOUNT) AS
        ( Select 1, 'cogs', 'a', 'camping', 100 From Dual Union All 
          Select 2, 'cogs', 'a', 'spatula', 150 From Dual Union All 
          Select 3, 'cogs', 'b', 'camping', 200 From Dual Union All 
          Select 4, 'cogs', 'b', 'spatula', 100 From Dual Union All 
          Select 5, 'sales', 'a', 'camping', 225 From Dual Union All 
          Select 6, 'sales', 'a', 'spatula',  75 From Dual Union All 
          Select 5, 'sales', 'b', 'camping', 250 From Dual Union All 
          Select 6, 'sales', 'b', 'spatula', 100 From Dual 
        )

--   M a i n    S Q L :
Select LOCATION, Sum(COGS_TOTAL) "COGS_TOTAL", Sum(SALES_TOTAL) "SALES_TOTAL"
From   tbl
  PIVOT ( Sum(AMOUNT) "TOTAL" FOR ACCOUNT IN('cogs' "COGS", 'sales' "SALES") )
Group By LOCATION
Order By LOCATION

/*    R e s u l t :
LOCATION COGS_TOTAL SALES_TOTAL
-------- ---------- -----------
a               250         300
b               300         350  */

In your second table sample data are a bit different...

--  Your second table
WITH    --  S a m p l e    D a t a :
    tbl (ID, ACCOUNT, LOCATION, DEPT, AMOUNT) AS
        ( Select 1, 'cogs', 'a', 'camping', 100 From Dual Union All 
          Select 2, 'cogs', 'a', 'spatula', 150 From Dual Union All 
          Select 3, 'cogs', 'b', 'camping', 200 From Dual Union All 
          Select 4, 'cogs', 'b', 'spatula', 100 From Dual Union All 
          Select 5, 'sales', 'a', 'camping', 150 From Dual Union All 
          Select 6, 'sales', 'a', 'spatula', 200 From Dual Union All 
          Select 5, 'sales', 'b', 'camping', 250 From Dual Union All 
          Select 6, 'sales', 'b', 'spatula', 150 From Dual 
        )

--   M a i n    S Q L : 
Select    LOCATION, DEPT, 
          Sum(COGS_TOTAL) "COGS_TOTAL", Sum(SALES_TOTAL) "SALES_TOTAL",
          Round(((Sum(SALES_TOTAL) - Sum(COGS_TOTAL)) / Sum(SALES_TOTAL)) * 100, 0) "GROSS_MARGIN_PCT"
From      tbl
          PIVOT ( Sum(AMOUNT) "TOTAL" FOR ACCOUNT IN('cogs' "COGS", 'sales' "SALES") )
Group By LOCATION, DEPT
Order By LOCATION, DEPT

/*
LOCATION DEPT    COGS_TOTAL SALES_TOTAL GROSS_MARGIN_PCT
-------- ------- ---------- ----------- ----------------
a        camping        100         150               33
a        spatula        150         200               25
b        camping        200         250               20
b        spatula        100         150               33      */