Subject: Fwd: Re: Need help creating query statement 2007-11-05 - By Enrique Sanchez Vela
Back
--- Enrique Sanchez Vela <esanchezvela@(protected)>
wrote:
> Date: Mon, 5 Nov 2007 15:01:59 -0800 (PST)
> From: Enrique Sanchez Vela <esanchezvela@(protected)>
> Subject: Re: Need help creating query statement
> To: N?stor <rotsen@(protected)>
>
>
> --- N?stor <rotsen@(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/mysql?unsub=mysql@(protected)
|
|
