List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 29 1999 3:10am
Subject:Re: Doing Math
View as plain text  
On Fri, 1999-08-27 14:57:46 -0700, Thimble Smith wrote:

> At 17:03, 19990827, Wayne Spivak wrote:
> >Have a database that allows records to be placed into four different
> >categories of a possible 100 catagores.  This is done by haveing four
> >fields, Area0, 1, 2 & 3.
> >
> >I want to be able to tell my users how many in total, whether the info is
> >stored in Area1 or Area3, how many listing there are per category.
> >
> >Tried summing Count(area0+area1....) and that didn't work.
> >
> >Want to set variables and manually count, but couldn't figure out how you
> >can do this in MySQL.
> >
> >Any ideas?
> 
> You could normalize your data, and it would become trivial (but it
> might not be the best solution for you). [...]

Wayne, I also recommend to follow Thimble's advice.

But if you want to keep your current db scheme, I guess you've to
do the counting in five steps:  for each of the four area columns,
and then summing up the four counts.

Using a temporary table to hold the intermediate results, the counting
procedure might look like this:

  CREATE TABLE tmp (
     area   CHAR(20),  /* or whatever field type you have ... */
     number INT
  );
  LOCK TABLES tmp WRITE, yourtable READ;  /* to ensure consistency */
  INSERT INTO tmp
    SELECT Area0, COUNT(*) FROM yourtable GROUP BY Area0;
  INSERT INTO tmp
    SELECT Area1, COUNT(*) FROM yourtable GROUP BY Area1;
  INSERT INTO tmp
    SELECT Area2, COUNT(*) FROM yourtable GROUP BY Area2;
  INSERT INTO tmp
    SELECT Area3, COUNT(*) FROM yourtable GROUP BY Area3;
  SELECT area, SUM(number) AS number FROM tmp GROUP BY area;
  UNLOCK TABLES;
  DROP TABLE tmp;

In MySQL V3.23 you can also use CREATE TEMPORARY TABLE; for details
see the MySQL manual.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Doing MathWayne Spivak27 Aug
  • Re: Doing MathThimble Smith27 Aug
    • Re: Doing MathMartin Ramsch29 Aug
  • Re: Doing MathJoel Bremson1 Sep