How to output the results of a SQL query into a report form table?

Question

I have an the following table fields:

Invitations (user_id, type, created_at, completed_at)

I'm currently able to obtain last week's invitation conversation rate by running the following query and manually computing.

SELECT * 
FROM invitations
WHERE created_at >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND created_at < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
AND user_id != 1
AND type = 'email'
ORDER BY completed_at, created_at

Is it possible with SQL to output more of a report... Something that returns:

LAST WEEK | Total Invitations | Invitations Completed | % Conversion    
          | 100               | 50                    | 50%  
TWO WEEKS | Total Invitations | Invitations Completed | % Conversion    
          | 100               | 60                    | 60%

Is something like this possible with SQL or do I need to create this with application logic?

sticky bit · Accepted Answer · 2018-07-25T22:58:59.653

Maybe you want to aggregate using count() and do a UNION ALL.

SELECT 'LAST WEEK' `Period`,
       count(created_at) `Total Invitations`,
       count(completed_at) `Invitations completed`,
       concat(count(completed_at) / count(created_at) * 100, '%') `% Conversion` 
       FROM invitations
       WHERE created_at >= curdate() - INTERVAL dayofweek(curdate()) + 6 DAY
             AND created_at < curdate() - INTERVAL dayofweek(curdate()) - 1 DAY
             AND user_id <> 1
             AND type = 'email'
UNION ALL
SELECT 'TWO WEEKS' `Period`,
       count(created_at) `Total Invitations`,
       count(completed_at) `Invitations completed`,
       concat(count(completed_at) / count(created_at) * 100, '%') `% Conversion` 
       FROM invitations
       WHERE created_at >= curdate() - INTERVAL dayofweek(curdate()) + 13 DAY
             AND created_at < curdate() - INTERVAL dayofweek(curdate()) - 1 DAY
             AND user_id <> 1
             AND type = 'email';

count(completed_at) does only count the rows, where completed_at isn't null. I assume that completed_at is null if and only if the invitation isn't completed. count(created_at) works analog. But I assume there are no null values in that column (and if they were, those rows won't match the conditions in the WHERE clause, so they aren't even candidates for counting). UNION ALL just unites the two result sets (without eliminating duplicates, which aren't in there anyway, as at least the Period differs).

That is incredible - thank you – AnApprentice Jul 25 '18 at 22:58 — AnApprentice, Jul 25 '18 at 22:58

How to output the results of a SQL query into a report form table?

1 Answers1