question

nevada1978 avatar image
nevada1978 asked

Creating an Aggregate or Total field

This may seem simple but I am still quite new and have to learn how to translate our out-dated practices (Fox Pro and Excel 4.0) to SQL. I need to produce an aggregate or total item from a query. The underlying data does not contain an aggregate or total version of the data involved. Below is an example that should illuminate this. 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 The resulting report would look like this. 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
selectssmsgroup-by
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Adedba avatar image
Adedba answered
Looks like Area is a constant value in your results so I have hardcoded that in my example below, give this a try: SELECT '000000' AS [Area], Aggregates.[Size Class], Aggregates.[Employment in Size Class], Aggregates.[Number of Worksites] FROM ( SELECT [Size Class], SUM(Employment) AS [Employment in Size Class], COUNT([Size Class]) AS [Number of Worksites] FROM GROUP BY [Size Class] ) AS Aggregates;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.