CODEWITHSUNDEEP
sqlpostgresql
thiago
thiago

Reputation: 385

SQL query inside another query?

I have this table:

total user wallet storagesummary chain
40 user1 wallet1 2 1
30 user1 wallet1 4 1
8 user1 wallet2 1 1
2 user2 wallet3 3 1
41 user2 wallet3 4 3

And this is what I'm trying to accomplish:

enter image description here

Type1, Type2, n... columns are a combination of storagesummary and chain

Just for the sake of example: if storagesummary == 2 and chain == 1, then it's gonna be Type1.

I tried something like this, but I don't think it's the best approach and I dunno what to do with the user/wallet columns:

SELECT (SELECT total from MyTable where storagesummary = 2 and chain == 1) as Total Type1 Count,
       (SELECT total from MyTable where storagesummary = 4 and chain == 1) as Total Type2 Count,
      .......

I cant' figure out how to achieve this.

Upvotes: 0

Views: 42

Answers (1)

LukStorms
LukStorms

Reputation: 29657

This could use a bit of conditional aggregation to pivot that.

SELECT 
  CONCAT(t.user, ' / ', t.wallet) AS "User Wallet Address"
, SUM(CASE 
      WHEN t.storagesummary = 2 AND t.chain = 1 
        THEN t.total
      ELSE 0
      END) AS "Total Type1"
, SUM(CASE 
      WHEN t.storagesummary = 4 AND t.chain = 3
        THEN t.total 
      WHEN t.storagesummary = 1 AND t.chain = 1
        THEN t.total
      ELSE 0
      END) AS "Total Type2"
, SUM(CASE 
      WHEN t.storagesummary IN(2,5) AND t.chain >= 2 
        THEN t.total
      ELSE 0
      END) AS "Total Type3"
FROM MyTable t
GROUP BY t.user, t.wallet
ORDER BY t.user, t.wallet

Not sure if you need SUM or MAX though.
And the logic in the CASE's will needs corrections.
But if you get the concept, then you'll understand what to change.

Upvotes: 2

Related Questions