Re: How to work around count discarding null rows?

From: Sergey Balter <balter_at_kompas.donetsk.ua>
Date: Tue, 29 Oct 2002 17:01:40 +0200
Message-ID: <apm7tk$mba$1@dipt.donbass.net>

Hi, Matt,

At first lets check how I've understood your problem, Ok?

Well, you have a table of persons, for instance let call it STUDENT. Each person at the table is characterised with three attributes
(Grade, Major and Ethnicity).

You need to perform a query which returns ALL POSSIBLE combination of Grade, Major and Ethnicity and count the total quantity of persons that fit (match?) each combination.

Is it correct description of your task?

---

The FIRST POINT is to obtain a Decart Product of all grades, majors and ethnicities.
Then for each combination to calculate a quantity of person that match the grade+major+ethnicity

For eg., you can perform it with the following query:

Example 1.

---

SELECT G.Grade, M.Major, E.Ethnicity,
(SELECT Count(*) FROM STUDENT S WHERE

S.Grade = G.Grade AND S.Major = M.Major AND S.Ethnicity = E.Ethnicity) "Count"
FROM
(SELECT DISTINCT Grade FROM STUDENT WHERE Grade IS NOT NULL) G
(SELECT DISTINCT Major FROM STUDENT WHERE Major IS NOT NULL) M
(SELECT DISTINCT Ethnicity FROM STUDENT WHERE Ethnicity IS NOT NULL) E

---

The SECOND POINT is to obtain REALLY ALL possible values of grades, majors and ethnicities.
The solution shown above (Example 1) not a good because it depends of what exactly values of Grade, Major and Ethnicity are ALREADY ENTERED into STUDENT table.

Usually developers design separate tables for all grades as well as majors and ethnicities :-)))
something like the following:

CREATE TABLE GRADES(
  Id Number -- unique Id of grade   Name VarChar2(40) -- name or title
  );
ALTER TABLE GRADES ADD Primary Key (Id)

Let for instance that others table have a similar structure.

In that case it's possible to transform Example 1 to...

Example 2

---

SELECT G.Id "Grade", M.Id "Major", E.Id "Ethnicity",
(SELECT Count(*) FROM STUDENT S WHERE

  S.Grade = G.Id AND S.Major = M.Id AND S.Ethnicity = E.Id) "Count" FROM
GRADES G, MAJORS M, ETHNICITIES E It's better.

Bye.

   Regards,
   Segrey Balter
balter_at_kompas.donetsk.ua

P. S. Sorry for pure English.
Don't hesitate to ask me if something is still foggy :-)). Received on Tue Oct 29 2002 - 09:01:40 CST