Subject: Re: Need help creating query statement 2007-11-06 - By mysql@(protected)
Back
N?stor wrote:
> I can do simple select statements but I need your brains to create query
> statment.
> I am using mysql 4.0 in my 1and1 site.
>
> I have a table that has 8 fields, one is the "agency" field and the other 7
> are
> *tip* values on saving water and the value of this field is either 0 or an
> amount.
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
> |agency| 5g | 8g | 10g | 12g | 15g | 20g | 40g |
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
>
> so the record could be
> |Helix | 0 | 8 | 10 | 12 | 15 | 0 | 40 |
> |Corp | 5 | 0 | 0 | 12 | 15 | 0 | 0 |
> |Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 |
>
> I need to get the to count *tips* per *agency
> *so in this case of 3 records I would have:
> |Helix | 0 | 2 | 2 | 1 | 2 | 0 | 2 |
> |Corp | 1 | 0 | 0 | 1 | 1 | 0 | 0 |
>
> and then I need to come out with top 5 *tips *per agency
> |Helix | 8g = 2 | 10g = 2 | 15g = 2 | 8g = 2 | 40g = 2|
> |Corp | 8g = 1 | 12g = 1 | 15g= 1 |
>
> Is there an easy way to do this?
>
I think you'd best begin by normalising your database. Something along
these lines:
CREATE TABLE agency (
id tinyint(3) unsigned NOT NULL auto_increment,
name varchar(64)NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;
CREATE TABLE pledge (
id tinyint(3) unsigned NOT NULL auto_increment,
agency_id tinyint(3) unsigned NOT NULL,
gallons enum('5','8','10','12','15','20','40') DEFAULT NULL,
PRIMARY KEY (id),
FOREIGN KEY (agency_id) REFERENCES agency (id)
) ENGINE=MyISAM;
You could create a separate 'gallons' table, with the '5','8','10', etc.
as rows but, since this is MySQL you might as well go with the ENUM.
Once you have your data normalised you'll be able to things like joining
across both tables and grouping by agency, etc.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
