sqloracle-databasesuiteql

Aggregate subset of data in separate columns without subquery

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.

Solution

  • 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