CODEWITHSUNDEEP
sqlsqlite
sqrj
sqrj

Reputation: 11

Grouping twice by different criteria, same column

I have data with the following columns: 

OFFICER_ID, CLIENT_ID, SECURITY_CODE, POSITION_SIZE

and then per each row for instance:

officer1, client100, securityZYX, $100k,
officer2, client124, securityADF, $200k,
officer1, client130, securityARR, $150k,
officer4, client452, securityADF, $200k,
officer2, client124, securityARR, $500k,
officer7, client108, securityZYX, $223k, 
and so on.

As you see, each client has a single officer assigned to either buy o sell securities, but each client can have bought different securities.

Apart from ranking officers by total amount in US$ of securities held by their clients (which I've done) I need to create ranges of total client accounts by adding total securities held by client ID, for example, total securities held sum < $1million, between $1million and $3million and > $3 million.

I've tried:

SELECT officer_ID, SUM(position_size) as AUM
FROM trades
GROUP BY client_ID
HAVING AUM > 1000000 AND AUM < 3000000;

and I get a list of all officers appearing several times, no totals.

I'd need a simple:

Officer_ID | range < 1m | range 1m-3m | range > 3m

officer1, [total amount of client accounts with securities adding up < 1m totals], [total amount of client accounts with securities adding up between 1m and 3m totals], etc.

Please, would you point me in the right direction?

UPDATE

I modified Tim's suggested code and obtained the desired output:

SELECT
    OFFICER_ID,
    SUM(CASE WHEN total < 1000000 THEN total END) AS "range < 1m",
    SUM(CASE WHEN total >= 1000000 AND total < 3000000 THEN total END) AS "range 1m-3m",
    SUM(CASE WHEN total >= 3000000 THEN total END) AS "range > 3m"
FROM
(
    SELECT OFFICER_ID, CLIENT_ID, SUM(POSITION_SIZE) AS total
    FROM trades
    GROUP BY OFFICER_ID, CLIENT_ID
) t
GROUP BY
    OFFICER_ID;

Too kind, Tim, thanks!

Upvotes: 1

Views: 41

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

We can try aggregating twice, first by both officer and client, to get the client totals, and a second time by officer alone, to get the counts:

SELECT
    OFFICER_ID,
    COUNT(CASE WHEN total < 1000000 THEN 1 END) AS "range < 1m",
    COUNT(CASE WHEN total >= 1000000 AND total < 3000000 THEN 1 END) AS "range 1m-3m",
    COUNT(CASE WHEN total >= 3000000 THEN 1 END) AS "range > 3m"
FROM
(
    SELECT OFFICER_ID, CLIENT_ID, SUM(POSITION_SIZE) AS total
    FROM trades
    GROUP BY OFFICER_ID, CLIENT_ID
) t
GROUP BY
    OFFICER_ID;

Upvotes: 2

Related Questions