Enrique that is pretty good and close to what I need. On top of what you have generously provide, I guess I can just put each gallon field into an array an then sort the array to display the first 5 gallon fields per agency. Is there an easier way?
Thanks, Nestor :-) On 11/5/07, Enrique Sanchez Vela <[EMAIL PROTECTED]> wrote: > > > --- Néstor <[EMAIL PROTECTED]> 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 | > > > > let's see if I got the point.... > > CREATE TABLE `agency` ( > `name` varchar(11) collate latin1_bin NOT NULL > default '', > `5g` int(11) NOT NULL default '0', > `8g` int(11) NOT NULL default '0', > `10g` int(11) NOT NULL default '0', > `12g` int(11) NOT NULL default '0', > `15g` int(11) NOT NULL default '0', > `20g` int(11) NOT NULL default '0', > `40g` int(11) NOT NULL default '0' > ) ENGINE=MyISAM ; > > > select * from agency; > > +-------+----+----+-----+-----+-----+-----+-----+ > | name | 5g | 8g | 10g | 12g | 15g | 20g | 40g | > +-------+----+----+-----+-----+-----+-----+-----+ > | Helix | 0 | 0 | 0 | 12 | 0 | 0 | 0 | > | Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | > | Corp | 5 | 0 | 0 | 12 | 15 | 0 | 40 | > | Helix | 0 | 8 | 10 | 0 | 15 | 0 | 40 | > +-------+----+----+-----+-----+-----+-----+-----+ > 4 rows in set (0.00 sec) > > > select `name`, sum(if(5g>0, 1,0)) as 5g , > sum(if(8g>0, 1,0)) as 8g, sum(if(10g>0,1,0)) as 10g, > sum(if(12g>0,1,0))as 12g ,sum(if(15g>0,1,0)) as 15g , > sum(if (20g>0,1,0)) as 20g, sum(if(40g>0,1,0)) as 40g > from agency group by `name`; > > produces... > > +-------+------+------+------+------+------+------+------+ > | name | 5g | 8g | 10g | 12g | 15g | 20g | > 40g | > +-------+------+------+------+------+------+------+------+ > | Corp | 1 | 0 | 0 | 1 | 1 | 0 | > 1 | > | Helix | 0 | 2 | 2 | 1 | 2 | 0 | > 2 | > +-------+------+------+------+------+------+------+------+ > 2 rows in set (0.00 sec) > > regards, > esv. > > > -------------------------------------- > "What you have been obliged to discover > by yourself leaves a path in your mind > which you can use again when the need > arises." --G. C. Lichtenberg > > http://themathcircle.org/ > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com >
