Counting Confirms and Attendees in Query to Display in Report

[Alb58]

I want to make a report for event attendance based on how many confirms and attendees were obtained by a caller. I made a query where the first column is Name of Caller, the second is Event Name, the third is Attendee Name and fourth and fifth are boolean variables for Confirmed to Attend and Attended. I can run a query using the count function and that gives me only a total number of either confirms or attendees for each caller but not both totals for each caller.

I could solve this if there was a function in the report builder counting the number of TRUEs but I haven’t found one that does this. I could also solve this by replacing the TRUE/FALSE value to a 1 or 0 integer value in the query I made by running another query on this first one as a subquery.

Does anyone know if there is a way to do either of the above?

[Ratslinger]

Boolean values are TRUE/FALSE and 1/0. If Confirmed is like saying the Confirmed is TRUE or 1. Your answer is all in the SQL statement. You can enter your statement similar to:

SELECT "Caller","Event", SUM(CASE WHEN "Confirmed" Then 1 Else 0 End) AS "TotalConfirmed", SUM(CASE WHEN "Attended" Then 1 Else 0 End) AS "TotalAttended" FROM "Table" AS "Table" GROUP BY "Caller", "Event" ORDER BY "Caller" ASC

Enter your query statement directly in SQL view and turn on ‘Run SQL command directly’ using toolbar button ‘SQL’.

[Alb58]

Thank you. I used the CASE statement and then added the number of confirms and attendees in the report. One needs to write “Confirmed” and “Attended” between CASE and WHEN, correct?

[Ratslinger]

Nothing goes between CASE & WHEN. CASE is checking (using WHEN) if field “Confirmed/Attended” is ON & if so, adds 1 to the count of “TotalConfirmed/TotalAttended” which is what is to be used in your report. The structure of the statement is good as is & should only require you to use your actual field/table names.

[Alb58]

I might have used a slightly different version of the statement. I wrote CASE “Confirmed” WHEN ‘TRUE’ THEN 1 ELSE 0 END “ConfirmedInt”. I then did a SUM of all ConfirmedInt in the report itself. In any case the use of the CASE statement did it. thanks!

[Ratslinger]

Thank you for the feedback. Whatever way works best for you is what you should use. It is always a pleasure to hear back that the problem is resolved (regardless of who solved it) instead of no response or even that the answer is accepted.