Error with SUM
Hi, The following query gives an error in MySql: SELECT SUM((SELECT SUM(a.id) FROM users AS b)) FROM users AS a Error: # - Invalid use of group function I know the query doesn't really make sense and it is easy to rewrite it, but the error occurred in some generated sql (this is obviously just a stripped version of it) and I want to know what the reason for the error is, so I can change the sql generator. I already tried several fixes, and surprisingly, the following worked: SELECT SUM((SELECT SUM(a.id*IF(b.id=0,1,1)) FROM users AS b)) FROM users AS a To me, it seems that it fails, because the expression within the SUM part is independant of the query in which it aggregates, or something like that. Can anyone explain / clarify this and point me to where this restriction is mentioned in the mysql documentation? Thanks, Erik-Jan Krijgsman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error with SUM
Hi, The following query gives an error in MySql: SELECT SUM((SELECT SUM(a.id) FROM users AS b)) FROM users AS a Error: # - Invalid use of group function I know the query doesn't really make sense and it is easy to rewrite it, but the error occurred in some generated sql (this is obviously just a stripped version of it) and I want to know what the reason for the error is, so I can change the sql generator. I already tried several fixes, and surprisingly, the following worked: SELECT SUM((SELECT SUM(a.id*IF(b.id=0,1,1)) FROM users AS b)) FROM users AS a To me, it seems that it fails, because the expression within the SUM part is independant of the query in which it aggregates, or something like that. Can anyone explain / clarify this and point me to where this restriction is mentioned in the mysql documentation? Thanks, Erik-Jan Krijgsman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY problem
- Original Message - Subject: RE: ORDER BY problem Try your query with either back quotes around Company SELECT * FROM Contacts WHERE Categories="Services" and BusinessCodes REGEXP "^R" and gold_id="2" ORDER BY `Company` ASC Or no quotes around Company SELECT * FROM Contacts WHERE Categories="Services" and BusinessCodes REGEXP "^R" and gold_id="2" ORDER BY Company ASC -Original Message- From: Obantec Support [mailto:[EMAIL PROTECTED] Sent: Friday, July 11, 2008 9:29 AM To: mysql@lists.mysql.com Subject: ORDER BY problem Hi while testing an upgrade from 3.23.58 to 4.1.22 on an FC3 test box SELECT * FROM Contacts WHERE Categories="Services" and BusinessCodes REGEXP "^R" and gold_id="2" ORDER BY "Company" ASC on mysql server3.23.58 i get company result in ASC order. on mysql server4.1.22 i get non ASC order for companies. is there something on 4.1.22 i missed that effects ORDER BY? Mark Hi Rolando please only reply to the list. Ok fixed now by either method. I have chosen to leave the "" off. Thanks Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL License
Please change your password right away !!! It's in the message below !!! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 11, 2008 2:53 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: FW: MySQL License Hi, This is regarding MySQL license we had bought for Bharti Jersey Project. Could you please let me know the Warranty period for the same. Also, please let me know if we have any Maintenance and Support contract with MySQL against this license. If not, please provide me the quotation for the same as well. Meanwhile, please provide me contact details for getting support from MySQL. Regards Hemant >> >>>-Original Message- >>>From: ext MySQL Shop [mailto:[EMAIL PROTECTED] >>>Sent: 22 November, 2006 20:50 >>>To: Kerri Maria (Nokia-NET/Espoo) >>>Subject: MySQL License >>> >>>Dear Customer: >>> >>>Included is your MySQL Pro OEM License, ordered from MySQL AB. >>>License number(s): 635597-635598. >>> >>>Commercial downloads are available at http://mysql.mysql.com. >>> >>>Jersey Tele Net Limited can use this information to download a >>>commercial binary are http://mysql.mysql.com. >>>Login: Jersey63839 >>>Password: haqej23gu >>> >>>The following is your MySQL login and password. >>> >>>Login: Jersey63839 >>>Password: haqej23gu >>> >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORDER BY problem
Try your query with either back quotes around Company SELECT * FROM Contacts WHERE Categories="Services" and BusinessCodes REGEXP "^R" and gold_id="2" ORDER BY `Company` ASC Or no quotes around Company SELECT * FROM Contacts WHERE Categories="Services" and BusinessCodes REGEXP "^R" and gold_id="2" ORDER BY Company ASC -Original Message- From: Obantec Support [mailto:[EMAIL PROTECTED] Sent: Friday, July 11, 2008 9:29 AM To: mysql@lists.mysql.com Subject: ORDER BY problem Hi while testing an upgrade from 3.23.58 to 4.1.22 on an FC3 test box SELECT * FROM Contacts WHERE Categories="Services" and BusinessCodes REGEXP "^R" and gold_id="2" ORDER BY "Company" ASC on mysql server3.23.58 i get company result in ASC order. on mysql server4.1.22 i get non ASC order for companies. is there something on 4.1.22 i missed that effects ORDER BY? Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY problem
Hi while testing an upgrade from 3.23.58 to 4.1.22 on an FC3 test box SELECT * FROM Contacts WHERE Categories="Services" and BusinessCodes REGEXP "^R" and gold_id="2" ORDER BY "Company" ASC on mysql server3.23.58 i get company result in ASC order. on mysql server4.1.22 i get non ASC order for companies. is there something on 4.1.22 i missed that effects ORDER BY? Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count total number of records in db
Radoulov, Dimitre wrote:
mysql -NBe'show databases' |
while IFS= read -r db; do
printf "show tables from %s;\n" "$db" |
mysql -N | while IFS= read -r t; do
printf "select count(1) from %s.%s;\n" "$db" "$t"
done
done | mysql -N |
awk '{ s += $1 }END{ print s }'
I quickly put together a PHP script to do it. Its dirty( purpose built
:-) ) but it works.
or die("Couldn't connect to MySQL:" . mysql_error() . "" .
mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
or die("Couldn't select database:" . mysql_error(). "" .
mysql_errno());
$tables = mysql_list_tables($DB_DBName);
$count = 0;
$total_rows = 0;
while ($count < mysql_numrows($tables)) {
$table_name = mysql_tablename($tables,$count);
$sql = 'SELECT COUNT(*) FROM '.$table_name;
$result = mysql_query($sql);
$table_count = mysql_fetch_row($result);
$table_count = $table_count[0];
$total_rows = $total_rows + $table_count;
echo 'Number of rows in '.$table_name.' =
'.$table_count.'';
$count++;
}
echo '';
echo 'Total number of rows in database: '.$total_rows;
?>
Thanks guys.
Warren
--
Open Source Developer
Business Data Solutions
Email: [EMAIL PROTECTED]
Gmail: wwindvogel
MSN: wwindvogel
Skype: wwindvogel
Cell: 27 73 264 6700
Tel: 27 21 487 2177
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count total number of records in db
Warren Windvogel wrote:
Hi,
Can anyone tell me how to check the total number of records in a
database in MySQL version 4.0
Googling doesn't seem to help and all previous posts assume version 5.*
[...]
Something like this:
mysql -NBe'show databases' |
while IFS= read -r db; do
printf "show tables from %s;\n" "$db" |
mysql -N | while IFS= read -r t; do
printf "select count(1) from %s.%s;\n" "$db" "$t"
done
done | mysql -N |
awk '{ s += $1 }END{ print s }'
Regards
Dimitre
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: MySQL License
Hi, This is regarding MySQL license we had bought for Bharti Jersey Project. Could you please let me know the Warranty period for the same. Also, please let me know if we have any Maintenance and Support contract with MySQL against this license. If not, please provide me the quotation for the same as well. Meanwhile, please provide me contact details for getting support from MySQL. Regards Hemant >> >>>-Original Message- >>>From: ext MySQL Shop [mailto:[EMAIL PROTECTED] >>>Sent: 22 November, 2006 20:50 >>>To: Kerri Maria (Nokia-NET/Espoo) >>>Subject: MySQL License >>> >>>Dear Customer: >>> >>>Included is your MySQL Pro OEM License, ordered from MySQL AB. >>>License number(s): 635597-635598. >>> >>>Commercial downloads are available at http://mysql.mysql.com. >>> >>>Jersey Tele Net Limited can use this information to download a >>>commercial binary are http://mysql.mysql.com. >>>Login: Jersey63839 >>>Password: haqej23gu >>> >>>The following is your MySQL login and password. >>> >>>Login: Jersey63839 >>>Password: haqej23gu >>> >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count total number of records in db
Warren Windvogel wrote: > Hi, > > Can anyone tell me how to check the total number of records in a > database in MySQL version 4.0 > Googling doesn't seem to help and all previous posts assume version 5.* > > Regards > Warren > for table you can use: SELECT COUNT(*) from the_table_name; in whole database you can iterate trough all tables, for that create simple bash script. For example: #!/bin/bash tables=`mysql -uUser -pPass -hHost database_name --skip-column-names -e "SHOW TABLES"` total=0 for table in $tables do rows=`mysql -uUser -pPass -hHost database_name --skip-column-names -e "SELECT COUNT(*) from $table"` total=$(($total+rows)) done echo $total -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Count total number of records in db
Hi, Can anyone tell me how to check the total number of records in a database in MySQL version 4.0 Googling doesn't seem to help and all previous posts assume version 5.* Regards Warren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
