--- Enrique Sanchez Vela <[EMAIL PROTECTED]> wrote: > Date: Mon, 5 Nov 2007 15:01:59 -0800 (PST) > From: Enrique Sanchez Vela <[EMAIL PROTECTED]> > Subject: Re: Need help creating query statement > To: Néstor <[EMAIL PROTECTED]> > > > --- 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 >
-------------------------------------- "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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
