Announcement

**TheSmitchell** · 06-28-2013, 03:51 PM

Re: Count records question in MySQL

Does this work?

Code:

SELECT COUNT(*) AS Count_current_Dates
FROM activity_log_new
WHERE CurrentDate() = activity_log_new.DATE_CREATED

edit:

You could also do this:

Code:

SUM( IF (CurrentDate() = activity_log_new.DATE_CREATED, 1, 0) ) AS Count_current_Dates,

No idea what kind of impact a sum ( if (...) ) is going to have on your query. But ^^^ in case you can't use the where clause. This assumes that I used the SUM function properly...

**Keith Hubert** · 06-28-2013, 04:59 PM

Re: Count records question in MySQL

Hi Sarah,

Thanks for getting back to me on this.

I assume the WHERE creates a filter. I already have a very complex filter on the parent table, not sure how to fit this into what I already have.

Your second example looks like it returns a value, maybe I could use that as an indicator rather than an actual count.

**TheSmitchell** · 06-28-2013, 05:11 PM

Re: Count records question in MySQL

I went and looked at COUNT. It counts things that are not NULL. So if you modified your test to this:

Code:

COUNT( IF(currentdate() = activity_log_new.DATE_CREATED, 1, NULL) )  AS Count_current_Dates,

it should give you the count you're looking for.

**Keith Hubert** · 07-02-2013, 08:07 AM

Re: Count records question in MySQL

Hi Sarah,

Many thanks for your explanation. This works perfectly. I'm now able to change an image if the value is greater than 0.

The object of the application is to monitor phone calls made to members. So, in addition to counting calls made today we would like to display a field value, if the call was NOT answered.

The choices in the Contact_Result field are;
No Answer No Message
No Answer Message Left
Call Engaged
Answered Add Action
Answered Add Note

Please can you help. I did try to IM you but you have blocked that avenue.

**TheSmitchell** · 07-02-2013, 09:09 AM

Re: Count records question in MySQL

I did turn off IM. I don't like it.

If the call is "Call Engaged", does that mean the phone was answered? You can use the IF to create that field. I assume your No Answer No Message and No Answer Message Left are TINYTINT(1). If they are TINYINT(1) then 0 = false and 1 = true...

Code:

SELECT
    IF (NO_ANSWER_NO_MESSAGE = 1 OR NO_ANSWER_MESSAGE_LEFT = 1, "Unanswered", "Answered") AS WAS_ANSWERED
FROM Contact_Result;

If your No Answer No Message and No Answer Message Left columns are NULL if the call was answered, then you could test the NULL state instead:

Code:

SELECT
    IF ( NO_ANSWER_NO_MESSAGE IS NOT NULL OR NO_ANSWER_MESSAGE_LEFT IS NOT NULL, "Unanswered", "Answered") AS WAS_ANSWERED
FROM Contact_Result;

Without knowing what states indicate the call was answered - not much more I can assist with here! This should help you get started, though. You can put whatever test case you need in the IF to determine if the call was Answered or Unanswered. You can also just return a 0 or 1 in the IF. Alpha will treat the field as a logical field if you return a 1 or 0.

If you're writing portable SQL queries, documentation on IF is here: http://wiki.alphasoftware.com/Portability+Functions

If you're writing MySQL specific queries, IF is here: http://dev.mysql.com/doc/refman/5.5/...functions.html

**TheSmitchell** · 07-02-2013, 09:10 AM

Re: Count records question in MySQL

If the call is "Call Engaged", does that mean the phone was answered? You can use the IF to create that field. I assume your No Answer No Message and No Answer Message Left are TINYTINT(1). If they are TINYINT(1) then 0 = false and 1 = true...

Code:

SELECT
    IF (NO_ANSWER_NO_MESSAGE = 1 OR NO_ANSWER_MESSAGE_LEFT = 1, "Unanswered", "Answered") AS WAS_ANSWERED
FROM Contact_Result;

If your No Answer No Message and No Answer Message Left columns are NULL if the call was answered, then you could test the NULL state instead:

Code:

SELECT
    IF ( NO_ANSWER_NO_MESSAGE IS NOT NULL OR NO_ANSWER_MESSAGE_LEFT IS NOT NULL, "Unanswered", "Answered") AS WAS_ANSWERED
FROM Contact_Result;

Without knowing what states indicate the call was answered - not much more I can assist with here! This should help you get started, though. You can put whatever test case you need in the IF to determine if the call was Answered or Unanswered. You can also just return a 0 or 1 in the IF. Alpha will treat the field as a logical field if you return a 1 or 0.

If you're writing portable SQL queries, documentation on IF is here: http://wiki.alphasoftware.com/Portability+Functions

If you're writing MySQL specific queries, IF is here: http://dev.mysql.com/doc/refman/5.5/...functions.html

**TheSmitchell** · 07-02-2013, 09:14 AM

Re: Count records question in MySQL

Originally posted by Keith Hubert View Post

Hi Sarah,

Many thanks for your explanation. This works perfectly. I'm now able to change an image if the value is greater than 0.

The object of the application is to monitor phone calls made to members. So, in addition to counting calls made today we would like to display a field value, if the call was NOT answered.

The choices in the Contact_Result field are;
No Answer No Message
No Answer Message Left
Call Engaged
Answered Add Action
Answered Add Note

Please can you help. I did try to IM you but you have blocked that avenue.

I did turn off IM. I don't like using it.

You can use an IF to test your data fields to create a new field that contains the information you're looking for:

Code:

SELECT
    IF(Contact_Result IN ('No Answer No Message','No Answer Message Left'), 'Unanswered', 'Answered') AS WAS_ANSWERED
FROM ... ? ...

I suppose you could also use LIKE...

Code:

    IF(Contact_Result LIKE 'No Answer%', 'Unanswered', 'Answered') AS WAS_ANSWERED

It's a toss-up on LIKE. I like IN better. It's more explicit, despite it's wordiness.

Woo. I attempted to post on this twice earlier. Got a message that the post had to be reviewed by a moderator first - but logging out & logging back in permits me to post... Not sure if I'm being moderated or if I'm getting bad BB sessions? If they show up - they're wrong. I'll clear them out if they show up.

**Keith Hubert** · 07-02-2013, 01:42 PM

Re: Count records question in MySQL

Hi Sarah,

Thank you for your samples.

I used

Code:

IF(activity_log_new.Contact_Result IN ('No Answer No Message', 'No Answer Message Left', 'Call Engaged') , 'Call Back', 'Answered')  AS NOT_ANSWERED,

The result I get is Answered for all records, even if no call has been made. Or for example, the Contact_Result = Call Engaged.

Where have I done wrong?

**TheSmitchell** · 07-02-2013, 02:15 PM

Re: Count records question in MySQL

If you change your IF expression to this:

Code:

IF(activity_log_new.Contact_Result = 'No Answer No Message', 'Call Back', 'Answered') AS NOT_ANSWERED

...do you get desired results for records where Contact_Result = 'No Answer No Message'?

**Keith Hubert** · 07-02-2013, 05:06 PM

Re: Count records question in MySQL

I used,

Code:

  IF( Contact_Result = 'Call Engaged' , 'Call Back', 'Answered')  AS NOT_ANSWERED

This gave me Answered in all records.

**chadbrown** · 07-02-2013, 09:39 PM

Re: Count records question in MySQL

Keith are you trying to get a count of how many record on each day were say engaged, call back and answered?

**Keith Hubert** · 07-02-2013, 11:42 PM

Re: Count records question in MySQL

Hi Chad,

Originally posted by chadbrown View Post

Keith are you trying to get a count of how many record on each day were say engaged, call back and answered?

Thanks for asking, but no to your question.

The idea is that a number of people have to be called on different days. The 'Callers', working online, will be looking at a call list. I'm trying to show on the call list the status of the 'Callee'. (new word?). ie If they have been called today and what was the Result of the Call. This is to try and prevent the same person being called unnecessarily.

**TheSmitchell** · 07-03-2013, 12:00 PM

Re: Count records question in MySQL

What's the value of Contact_Result, then? If you're getting "Answered" for all records, that means none of your Contact_Result values are 'Call Engaged', supposedly.

**Keith Hubert** · 07-03-2013, 01:46 PM

Re: Count records question in MySQL

Hi Sarah,

One record has "Call Engaged" in the table but not shown as such in the grid. You can see that the Red spot indicates a record was created for Today.
calls_answered.PNG
Only the call to Dr Franklin was made and he was Engaged. (No surprise there).

I think I might be doing doing this the wrong way, as there could be a number of calls, all I would need to know is the status of the last call made to that person today.

Announcement

The Alpha Software Forum Participation Guidelines

Count records question in MySQL

Count records question in MySQL

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment