Re: Order by price?
I want to count the passing and failing scores of a group of tests so I have a table with a row that describes each test Each test has a minimum passing score. Each test can be run an arbitrary number of times so I have a table of scores, which uses test id as a "foreign key". what I would like to do is count the count of fails and passes. ideally in a single query. so the test table lookes like this -- t_id . . . . min_pass_score --- and the score table looks like this: --- score_id t_id score . . . --- I would like to select so the result set looks like this: t_id no_passes no_fails - 1 5 6 2 12 4 3192 34 . . . How can I do this? I have access to Mysql 4.0.18 although the production server still runs 3.23 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Order by price?
Paul, I tried doing that in one trial run, and I wasn't getting the $200 figure so I gave up on that. It seems like doing an order by that statement wouldn't have given me the sort I wanted. Don't know why that is though - in theory you should be right, I can try it a bit more if your curious. Best, Yoed -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, April 29, 2004 12:28 PM To: Yoed Anis; [EMAIL PROTECTED] Subject: Re: Order by price? At 12:14 -0500 4/29/04, Yoed Anis wrote: >I'm having trouble with this query: >SELECT >CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC >),2)) >as price FROM table ORDER BY price > >A note first. The column "establishments" is an int(10), as you see I >am taking this number through a formula, and I want the result of this >formula to be rounded to two decimal places, and to stick a "$" sign in >front of it so I can easily display it on a table. > >However here is the problem, in ASC order I get these results: $110.46 >$173.86 >$208.02 >$22.62 >$22.62 > >And in DESC order I get these results: >$90.44 >$79.94 >$50.10 >$48.33 >(with the max result 208 for instance at the 3rd from very bottom, 110 >is at very bottom) Sounds like you want: ORDER BY ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- 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]
Re: Order by price?
Paul DuBois wrote: *snip* Sounds like you want: ORDER BY ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) Perfect. (I'm saying, "duh" over here to myself!) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Order by price?
Both of you are right Daniel, and Robert. I've written a PHP class that "tabelizes" the query directly and I haven't built in formatting options. Doing so would make the object more complicated then I would like it to be, so I wanted to see if there is a mysql solution to this. I think I'll end up cheating out of this solution by simply ordering by establishments instead of price, price will always be higher for the higher # of establishments (I don't have any # of establishments higher than the maximum of the quadratic equatiosn). Maybe not elegant, but it'll work :-) Thanks guys, Best, Yoed -Original Message- From: Robert J Taylor [mailto:[EMAIL PROTECTED] Sent: Thursday, April 29, 2004 12:23 PM To: Yoed Anis; [EMAIL PROTECTED] Subject: Re: Order by price? Yoed Anis wrote: >I'm having trouble with this query: >SELECT >CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC >),2)) >as price FROM table ORDER BY price > > > Hint: you've made this a "string" comparison using CONCAT, i.e., alphabetical, not numerical, sorting rules apply. How about SELECT ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) as price FROM table ORDER BY price; Then using a printf/sprintf type formatting statement on the application/presentation layer to show to the user? What is the application language y >A note first. The column "establishments" is an int(10), as you see I >am taking this number through a formula, and I want the result of this >formula to be rounded to two decimal places, and to stick a "$" sign in >front of it so I can easily display it on a table. > >However here is the problem, in ASC order I get these results: $110.46 >$173.86 >$208.02 >$22.62 >$22.62 > >And in DESC order I get these results: >$90.44 >$79.94 >$50.10 >$48.33 >(with the max result 208 for instance at the 3rd from very bottom, 110 is at >very bottom) > > >Any help in solving this would be greatly appreciated. >Thanks, >Yoed > > > > HTH, Robert Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by price?
At 12:14 -0500 4/29/04, Yoed Anis wrote: I'm having trouble with this query: SELECT CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2)) as price FROM table ORDER BY price A note first. The column "establishments" is an int(10), as you see I am taking this number through a formula, and I want the result of this formula to be rounded to two decimal places, and to stick a "$" sign in front of it so I can easily display it on a table. However here is the problem, in ASC order I get these results: $110.46 $173.86 $208.02 $22.62 $22.62 And in DESC order I get these results: $90.44 $79.94 $50.10 $48.33 (with the max result 208 for instance at the 3rd from very bottom, 110 is at very bottom) Sounds like you want: ORDER BY ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by price?
Yoed Anis wrote: I'm having trouble with this query: SELECT CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2)) as price FROM table ORDER BY price Hint: you've made this a "string" comparison using CONCAT, i.e., alphabetical, not numerical, sorting rules apply. How about SELECT ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) as price FROM table ORDER BY price; Then using a printf/sprintf type formatting statement on the application/presentation layer to show to the user? What is the application language y A note first. The column "establishments" is an int(10), as you see I am taking this number through a formula, and I want the result of this formula to be rounded to two decimal places, and to stick a "$" sign in front of it so I can easily display it on a table. However here is the problem, in ASC order I get these results: $110.46 $173.86 $208.02 $22.62 $22.62 And in DESC order I get these results: $90.44 $79.94 $50.10 $48.33 (with the max result 208 for instance at the 3rd from very bottom, 110 is at very bottom) Any help in solving this would be greatly appreciated. Thanks, Yoed HTH, Robert Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by price?
I think when adding the $ the number gets convert to a string. And then the query orders it by alpha numberic. > I'm having trouble with this query: > SELECT > CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2)) > as price FROM table ORDER BY price > > A note first. The column "establishments" is an int(10), as you see I am > taking this number through a formula, and I want the result of this > formula > to be rounded to two decimal places, and to stick a "$" sign in front of > it > so I can easily display it on a table. > > However here is the problem, in ASC order I get these results: > $110.46 > $173.86 > $208.02 > $22.62 > $22.62 > > And in DESC order I get these results: > $90.44 > $79.94 > $50.10 > $48.33 > (with the max result 208 for instance at the 3rd from very bottom, 110 is > at > very bottom) > > > Any help in solving this would be greatly appreciated. > Thanks, > Yoed > > > -- > 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]