I have this table:

And this is what I'm trying to accomplish:

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.

CodePudding user response：

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.

CodePudding user response：

I think what you are missing is matching internal column names with outer select, for example:

select action_id, next_action_id from ( select action_id, lead(action_id) over (order by action_id) next_action_id from action) where action_id <> next_action_id-1;

that's for oracle db, but normal SQL can fit without next/lead thing.