SQL calculate percent group by

  • Thread starter Thread starter db:southcongress
  • Start date Start date
D

db:southcongress

i was hoping someone could help with my problem:

my table (histres) has 9,000 some records grouped into districts
[histres.histdist]. One of the attributes is "current function"
[cfunc_cat_a] and contains info like, domestic, commercial,
recreation, government, etc. I'd like to calculate the current
functions as percentages per group. so my results would be a table
that looks something like:

Group|[cfunc_cat_a]|Percent
---------------------
1 Domestic 59
1 Commercial 22
1 Government 19
2 Domestic 76
2 Commercial 19
2 Recreation 01
3 .......
3
n

thanks,
db
 
SELECT histdist, [cfunc_cat_a], Count(histdist) / (SELECT Count(histdist)
FROM histres As H2 WHERE H2.histdist = histres.histdist) AS Percent
FROM histres
GROUP BY histdist, [cfunc_cat_a]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
okay!! this is perfect

....which of your books should i get?


John Viescas said:
SELECT histdist, [cfunc_cat_a], Count(histdist) / (SELECT Count(histdist)
FROM histres As H2 WHERE H2.histdist = histres.histdist) AS Percent
FROM histres
GROUP BY histdist, [cfunc_cat_a]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
db:southcongress said:
i was hoping someone could help with my problem:

my table (histres) has 9,000 some records grouped into districts
[histres.histdist]. One of the attributes is "current function"
[cfunc_cat_a] and contains info like, domestic, commercial,
recreation, government, etc. I'd like to calculate the current
functions as percentages per group. so my results would be a table
that looks something like:

Group|[cfunc_cat_a]|Percent
---------------------
1 Domestic 59
1 Commercial 22
1 Government 19
2 Domestic 76
2 Commercial 19
2 Recreation 01
3 .......
3
n

thanks,
db
Click to expand...
Click to expand...
 
All three? <s>

Seriously, SQL Queries for Mere Mortals is geared toward teaching you
ANSI-Standard SQL. You'll be able to use most of what you learn in Access,
and, in fact, you can find almost all the examples in MDB databases on the
CD. (We also included SQL scripts and SQL Server 7 mdf files.)

The two Access books are geared toward Intermediate+ users. The 2003 book
also applies to 2002 - with the exception of a handful of enhancements that
are flagged in the book.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
db said:
okay!! this is perfect

...which of your books should i get?


John Viescas said:
SELECT histdist, [cfunc_cat_a], Count(histdist) / (SELECT Count(histdist)
FROM histres As H2 WHERE H2.histdist = histres.histdist) AS Percent
FROM histres
GROUP BY histdist, [cfunc_cat_a]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
db:southcongress said:
i was hoping someone could help with my problem:

my table (histres) has 9,000 some records grouped into districts
[histres.histdist]. One of the attributes is "current function"
[cfunc_cat_a] and contains info like, domestic, commercial,
recreation, government, etc. I'd like to calculate the current
functions as percentages per group. so my results would be a table
that looks something like:

Group|[cfunc_cat_a]|Percent
---------------------
1 Domestic 59
1 Commercial 22
1 Government 19
2 Domestic 76
2 Commercial 19
2 Recreation 01
3 .......
3
n

thanks,
db
Click to expand...
Click to expand...
Click to expand...
 
Back
Top