Mailing List
Home
Forum Home
MySQL General - General MySQL discussion
MySQL++ - Programming with the C++ API to MySQL
MaxDB - Everything about MaxDB, formerly known as SAP DB
ODBC - ODBC with the MySQL Connector/ODBC driver
MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP
Java Help - Mostly related to the MySQL Connector/J driver
Perl - Perl support for MySQL with DBI and DBD::mysql
GUI - MySQL GUI Tools
Announcement
Subjects
• Subject: mysql openssl Question
• ERROR 1045: Access denied for user: 'root@localhost ' (Using
password: NO)
• Update one field with more fields from another table
• Subject: Getting Identity after INSERT
• ERROR 2002: Can 't connect to local MySQL server through socket
• mysql test 4 1 fails with the gis test
• Subject: MySQL Cluster Software
• Downgrade Mysql from 4 to 3 23
• Mysql 4 0 Oracle Stored Procedure Trigger Conversion
• Can 't access mysql after kernel upgrade
• Executing MySQL Commands From Within C Program
• Comparing and writing out BLOBS
• Subject: Re: Preventing Duplicate Entries
• FULLTEXT query format question
• Strange behavior, Table Level Permission
• Does the binary log enabling affect the MySQL performances?
• mysql:it 's a db not a dbms how it 's possible?!
• mysql have same function mthod as Oracle decode()
 
Subject: Need help creating query statement

Subject: Need help creating query statement

2007-11-05       - By N?stor

 Back
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 hope I made myself understood.

Thanks,

N?stor :-)