Jump to content


Photo

Using an If clause in MySQL


  • Please log in to reply
6 replies to this topic

#1 Buyocat

Buyocat
  • Members
  • PipPipPip
  • Advanced Member
  • 267 posts

Posted 30 June 2006 - 06:47 PM

Can anyone tell me how to construct the following statment so it works?  Currently I get an error - at or around the "and" portion of it.

SELECT SUM(IF(s.started >= 1140211367 AND s.ended <= 1151779520)) as num_logs 
FROM fp_session_history s

I have tried using && as well with no luck.
Looking for some easy-to-use tools?  Try these, https://sourceforge....jects/utils-php -- I made them myself.  They're distinct tools which are easy to understand and use.  See some examples uses at http://www.anotherearlymorning.com

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 30 June 2006 - 07:23 PM

Perhaps, if I knew what you were tryin to do
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 shocker-z

shocker-z
  • Members
  • PipPipPip
  • Advanced Member
  • 864 posts
  • LocationNottingham

Posted 30 June 2006 - 07:30 PM

look at http://dev.mysql.com...ning-range.html

SELECT SUM(IF(s.started >= 1140211367 AND s.ended <= 1151779520)) as num_logs
FROM fp_session_history

you would not use as num_logs either as that wouldn't hold a value..

www: www.ukchat.ws | irc: irc.ukchat.ws chan: #blufudge

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 30 June 2006 - 07:37 PM

OK - guessing game - are you trying to count the rows where (s.started >= 1140211367 AND s.ended <= 1151779520) ?

if so,

SELECT COUNT(*) WHERE s.started >= 1140211367 AND s.ended <= 1151779520


Are you trying to get the total of another column depending on the IF condition.

If so the IF syntax is IF(condition, value if true, value if false)

SELECT SUM(IF(s.started >= 1140211367 AND s.ended <= 1151779520), anothercol, 0 ) as num_logs
FROM fp_session_history s
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 Buyocat

Buyocat
  • Members
  • PipPipPip
  • Advanced Member
  • 267 posts

Posted 30 June 2006 - 08:27 PM

Thanks for the hand fellas, Barand I ended up using what you suggested, although I discovered that this works and gets the same result...
SELECT SUM(IF(time1 >= timeA && time2 <= timeB), 1, 0) ...
(don't ask me what me the 1 and 0 do, I think that it adds one if the if is true and zero otherwise)  At any  rate that fetches the same result as
SELECT COUNT(*) ... WHERE time1 > sometime AND time2 < someothertime
I went with count, it just seems more readable.
Looking for some easy-to-use tools?  Try these, https://sourceforge....jects/utils-php -- I made them myself.  They're distinct tools which are easy to understand and use.  See some examples uses at http://www.anotherearlymorning.com

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 30 June 2006 - 09:38 PM

I'd go with the COUNT(*) WHERE option.

The times I'd use IF is when a client wants a weekly report like

[pre]Product          Monday  Tuesday  ....  Friday      Weekly_Total[/pre]

Then my query would be something like

SELECT product, SUM(IF weekday=1, sales, 0) as monday, SUM(IF weekday=2, sales, 0) as tuesday... etc
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 01 July 2006 - 06:02 AM

if barand makes a suggestion about a sql query, you'd be wise to listen.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users