Re: Need help creating query statement
Guys, just wanted to thank you again for helping me with the sql statement that I needed. I was able to sorted using php and I was able to display the correct result. Thanks again!!1 Nestor :-) On Nov 6, 2007 7:37 AM, Néstor [EMAIL PROTECTED] wrote: You guys are correct, that is exactly what happened. I must thing of this in the future. At this moment I have a lot of other projects to take care, that it is eaiser for me to read the information into an associative array with the columns and the values and sort the array and then print the top 5 values within each array. Thanks, Nestor :-) On 11/6/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote: --- [EMAIL PROTECTED] wrote: Néstor wrote: I think you'd best begin by normalising your database. Something along these lines: very true indeed, that would save you major headaches when right after finishing the demo, someone would say, can we add a 18 gallon pledge? what about a 25? as of the sort of columns per row, I believe it is not possible nor in the goals of MySQL to make it possible/easy. best regards, enrique. -- 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]
Re: Need help creating query statement
--- [EMAIL PROTECTED] wrote: Néstor wrote: I think you'd best begin by normalising your database. Something along these lines: very true indeed, that would save you major headaches when right after finishing the demo, someone would say, can we add a 18 gallon pledge? what about a 25? as of the sort of columns per row, I believe it is not possible nor in the goals of MySQL to make it possible/easy. best regards, enrique. -- 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]
Re: Need help creating query statement
You guys are correct, that is exactly what happened. I must thing of this in the future. At this moment I have a lot of other projects to take care, that it is eaiser for me to read the information into an associative array with the columns and the values and sort the array and then print the top 5 values within each array. Thanks, Nestor :-) On 11/6/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote: --- [EMAIL PROTECTED] wrote: Néstor wrote: I think you'd best begin by normalising your database. Something along these lines: very true indeed, that would save you major headaches when right after finishing the demo, someone would say, can we add a 18 gallon pledge? what about a 25? as of the sort of columns per row, I believe it is not possible nor in the goals of MySQL to make it possible/easy. best regards, enrique. -- 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]
Fwd: Re: Need help creating query statement
--- 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(5g0, 1,0)) as 5g , sum(if(8g0, 1,0)) as 8g, sum(if(10g0,1,0)) as 10g, sum(if(12g0,1,0))as 12g ,sum(if(15g0,1,0)) as 15g , sum(if (20g0,1,0)) as 20g, sum(if(40g0,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]
Re: Need help creating query statement
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(5g0, 1,0)) as 5g , sum(if(8g0, 1,0)) as 8g, sum(if(10g0,1,0)) as 10g, sum(if(12g0,1,0))as 12g ,sum(if(15g0,1,0)) as 15g , sum(if (20g0,1,0)) as 20g, sum(if(40g0,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
Re: Need help creating query statement
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/[EMAIL PROTECTED]
