A sign to refactor SQL

Note to fellow programmer: just wondering why the yearn for multiple subqueries when it seems a simple group by would suffice?

If you see multiple instances of the one table in a query, why not see if it can be re-factored.
Admittedly the query was originally querying from separate tables which got consolidated into a view, so the query pedigree was sharpened already - if that's the right term to use.

SELECT DISTINCT c.my_id
      ,c.my_name
      ,TO_CHAR(c.my_date, 'DD/MM/YYYY HH:MI AM') my_date
      ,(SELECT SUM(cl.my_total)
        FROM my_view cl
        WHERE cl.my_id = c.my_id) my_value
      ,(SELECT COUNT(1)
        FROM my_view cl
        WHERE cl.my_id = c.my_id
        AND   cl.special_nbr_field > 0) my_cnt
FROM   my_view c

vs

SELECT my_id
      ,my_name
      ,TO_CHAR(my_date, 'DD/MM/YYYY HH:MI AM') my_date
      ,SUM(my_total) my_value
      ,SUM(SIGN(special_nbr_field)) item_cnt
from my_view
group by my_id, my_date, my_name

Don't feel negative - I added an elegant use of SIGN


I fear my puns are getting worse...