Query Help

kev_79 · September 11th, 2003, 11:54 AM

This should be a simple one for someone. I need to count the number of records in a field that equal 16 in one field (as an id #) and then in another table, count the records that have 16 in an id field. I can't figure out how to do this. In the end, I am wanting to divide the amount from one table by the hole for a percentage.

Any help is appreciated.

Kevin

Steven · September 17th, 2003, 01:50 AM

This will give you the count of records where id=16

Code:

SELECT Count(id)
FROM Table1
HAVING id=16;

To combine the two tables, and get a percentage, there's quite a few different methods you could use - one example - which is possibly the worst, is:

Code:

SELECT  ([T2]/[T1])*100 As Percentage
FROM (SELECT Count(id) AS T1 from Table1 having id=16) As Tbl1, 
SELECT Count(id) AS T2 from Table2 having id=16) AS Tbl2;

Just as a note on calculating the percentages - you may prefer to multiply rather than divide, as dividing is a more complex command at machine level.
Of course, in a simple calculation it will only result in the answer taking a nanosecond or two longer, and the majority of compilers change division to multiplication, but mentioning it does pad out this reply which would otherwise just be me posting the code

I am a loud man with a very large hat. This means I am in charge