creating a total field in SqL

33 Views Asked by At

The issue is that I can't figure out how to sum up a column. Sounds rudimentary but that is the issue. The field in question, area, has no aggregate value. There are 17 distinct areas. I know how to produce the report for the 17 existing area values. I need to aggregate the employment by size class for ALL Areas. Below is an example of the data set. My question is how I creata an area (000000) that contains a total employment by size class and number of worksites.

 area     Employment     Size Class   
000001        12                      03
000001        22                      04
000003        16                      03
000003        26                      04
000005         4                      01
000005         6                      02
000005         13                     03
000005         28                     04



 Area              Size Class        Employment in Size Class   Number of Worksites
000000           01                           4                    1                       
000000           02                           6                    1                                 
000000           03                           41                   3                                
000000           04                           76                   3                                
1

There are 1 best solutions below

1
On BEST ANSWER
SELECT
    '000000' AS area,
    [Size Class],
    SUM(Employment) AS [Employment In Size Class],
    COUNT(*) AS [Number of Worksites]
FROM
    MyTable
GROUP BY
    [Size Class]