?
Solved

sum if mysql

Posted on 2012-03-20
1
Medium Priority
?
311 Views
Last Modified: 2012-03-21
Hi I am trying to do a calculation of the number of helpdesk calls that are open at the end of the day.

I am using the following

sum(if(FROM_UNIXTIME(maindb.closedatex,"%Y-%m-%d") > dates.date,1,0 ))

This checks if the close date of the call is greater than my date field. However if a call is still open then the closdate is displayed as "1970-01-01"

Does anyone know how I can add this into the statement above?
0
Comment
Question by:Dan560
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 37745157
if i understand you right you need

sum(if(FROM_UNIXTIME(maindb.closedatex,"%Y-%m-%d") > dates.date or maindb.closedatex=0, 1,0 ))

0 because 1970-01-01 00:00:00 translates to that in unix_timestamp.  or you can also do

"or from_unixtime(maindb.closedatex,'%Y-%m-%d') = '1970-01-01'"
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question