Welcome to WebmasterWorld Guest from 54.242.53.253

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

MySQL ORDER BY in conjunction with a SUM field

need to know how to represent SUM as either 1 or 0

     
2:18 am on Nov 10, 2009 (gmt 0)

Junior Member

5+ Year Member

joined:Aug 27, 2008
posts: 65
votes: 0


I have a sum(featured_program) function in a query on my website that sums the values in the featured_program field, which are all 1s and 0s.

What I would like to do is order the results by whether the sum(featured_program) field equals 0, or something greater than 0 - i.e., I believe, cast or convert the sum(featured_program) field as a boolean.

How do I do this? Have been trying to use "ORDER BY (CAST(sum(featured_program)) as BOOL) DESC" but keep getting errors.

Thanks!

3:30 am on Nov 10, 2009 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member themadscientist is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

joined:Apr 14, 2008
posts:2910
votes: 62


I don't remember off the top of my head for SUM(), but with COUNT() you can (simplified):

"SELECT COUNT(col) AS total WHERE something='stuff' ORDER BY total";

The key is using the alias for the ORDER BY...
I would try:

"SELECT SUM(col) AS total WHERE something='stuff' ORDER BY total";

Then refer to the manual if you don't get it. Like I said, I don't remember if this works with SUM() off the top of my head or not.

3:49 am on Nov 10, 2009 (gmt 0)

Junior Member

5+ Year Member

joined:Aug 27, 2008
posts: 65
votes: 0


Thanks, but wouldn't this just order it by the value in sum(featured_program)?

Let's say I had the following data:

id ¦ name ¦ sum(featured_program)
1 ¦ one ¦ 0
2 ¦ two ¦ 8
3 ¦ three ¦ 4
4 ¦ four ¦ 1
5 ¦ five ¦ 0

Let's say I order by sum(featured_program) DESC then name ASC. That would put #2 first, which is not what I want to do. I want to put #4 first, by treating all rows where sum(featured_program)>0 equally, for sorting purposes. Make sense? Am I misreading your reply?

Thanks!

4:21 am on Nov 10, 2009 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member themadscientist is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

joined:Apr 14, 2008
posts:2910
votes: 62


Something like this is closer to what you are looking for then (I think):

"SELECT id,name,SUM(featured_program) AS total GROUP BY name HAVING featured_program>0 ORDER BY total";

Completely untested, but very similar to something I use with a COUNT()... Hope it gives you some ideas or a direction to go.

You might have to edit the GROUP BY or cols selected depending on your situation...

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members