[thelist] Better way to do this (SQL)?

[Ken Schaefer]

Just be aware of the tradeoffs WRT to application performance. Here you are
having the database calculate things that you could just as easily calculate
in your presentation layer code.

Ultimately, you want to minimise the load on the database server (by
utilising set theory), and on your application server (by doing things in
the database that are a pain to do using non-set based application
languages).

In your case, you could return:

SELECT
    Sex,
    COUNT(*)
FROM
    test
GROUP BY
    Sex

and you would have
M 100
F 200

(or similar).

All the other values that you have are calculated values (based on the two
that you have the database generate). The total (for example) is the sum of
the two COUNT(*) values. The % are based on the total, plus each individual
number. Application layer code would be a great place to put this type of
code, rather than in your Access database (especially considering the
non-performant problems with Access)

Cheers
Ken


----- Original Message ----- 
From: <david.landy at somerfield.co.uk>
To: <thelist at lists.evolt.org>
Sent: Thursday, May 20, 2004 8:51 PM
Subject: RE: [thelist] Better way to do this (SQL)?


Thanks to John and Sarah for the inspiration:

SELECT Count(*) AS TotalBoth, (SELECT Count(*) FROM test WHERE sex = 'M') AS
TotalMale, (SELECT Count(*) FROM test WHERE sex = 'F') AS TotalFemale,
100*TotalMale/TotalBoth AS PctMale, 100*TotalFemale/TotalBoth AS PctFemale
FROM test

Works like a charm on Access over JDBC/ODBC.

So _this_ is the answer to how to run the queries I was stuck on last week!

Thanks again,

David