How can sql query be optimized?

Question

SQL

SELECT "r"."ticker_symbol" AS symbol, (SELECT COUNT(*) FROM "requests" "re" WHERE "re"."wallet_address" = :walletAddress AND "r"."ticker_symbol" = "re"."ticker_symbol" AND "re"."request_status" = :requestStatus) AS "pendingCount", (SELECT SUM("r"."amount")::varchar FROM "requests" "rew" WHERE "rew"."wallet_address" = :walletAddress AND "r"."ticker_symbol" = "rew"."ticker_symbol" AND "rew"."request_status" = :requestStatus) AS "expected" FROM "requests" "r" WHERE "r"."wallet_address" = :walletAddress GROUP BY "r"."ticker_symbol"

Could you tell me how it is possible to write a request of this type more beautifully?

Answer 1

Similarly, but many times faster it will be like this:

SELECT 
            "ticker_symbol" AS symbol, 
      count(case when "request_status" = :requestStatus then 1 else null end) "pendingCount",
      sum(case when "request_status" = :requestStatus then "amount" else null end)::varchar "expected"
  FROM "requests"  
  WHERE "wallet_address" = :walletAddress 
  GROUP BY "ticker_symbol"

If I didn't mess up.
And if zero values ​​​​are not needed, then it can be even simpler:

SELECT 
            "ticker_symbol" AS symbol, 
      count(*) "pendingCount",
      sum("amount")::varchar "expected"
  FROM "requests"  
  WHERE "wallet_address" = :walletAddress and "request_status" = :requestStatus
  GROUP BY "ticker_symbol"