How to add a sum column with respect to a group by

I have a table1 :

ZP      age      Sexe    Count
A       40       0       5
A       40       1       3
C       55       1       2

And i want to add a column wich sum the count column by grouping the first two variables :

ZP      age      Sexe    Count   Sum 
A       40       0       5       8
A       40       1       3       8
C       55       1       2       2

this is what i do :

CREATE TABLE table2 AS SELECT zp, age, SUM(count) FROM table1 GROUP BY zp, age

then :

CREATE TABLE table3 AS SELECT * FROM table1 NATURAL JOIN table2

But i have a feeling this is a sloppy way to do it. Do you know any better ways ? For example with no intermediates tables.

edit : i am using SQL through a proc sql in SAS

Answer

I’m not quite sure if there is a method for a single select statement but below will work without multiple create table statements:

data have;
    length ZP $3 age 3 Sexe $3 Count 3;
    input ZP $ age Sexe $ Count;
    datalines;
    A       40       0       5
    A       40       1       3
    C       55       1       2
    ;
run;

proc sql noprint;
    create table WANT as
    select a.*, b.SUM 
    from
        (select * from HAVE) a,
        (select ZP,sum(COUNT) as SUM from HAVE group by ZP) b
    where a.ZP = b.ZP;
quit;