CODEWITHSUNDEEP
mysqlsqldatabasesubquery
user12438439
user12438439

Reputation:

Chain 3 queries into one to export the data to a csv

So I been battling with this for a little quite a bit today. I have these three queries.

This gives me the percentage of valid emails. 

SELECT (sum(case when c_email IS not NULL then 1 end )*100.00)/
        COUNT( c_number) as percentage, d_id, d_name
FROM distinct_customers group by d_id, d_name;

This counts the number of customers 

select count(distinct c_number) from distinct_customers;

This counts the number of customers when email is null

select count(distinct c_number) from distinct_customers where and c_email is not null;

I am trying to chain these three queries and combine them into one so that I can generate a report in my application with that data in a csv.

Upvotes: 0

Views: 59

Answers (2)

Nick
Nick

Reputation: 147146

Other than the condition on your third query you could just add all the select values into one query. To work around that, you can use conditional aggregation. Note that since this is MySQL, you can take advantage of the fact that booleans are treated as 1 or 0 in a numeric context and do away with your CASE expression:

SELECT d_id, d_name,
       SUM(c_email IS NOT NULL) * 100.00 / COUNT(c_number) AS percentage,
       COUNT(DISTINCT c_number) AS total_customers,
       SUM(c_email IS NULL) AS null_emails
FROM distinct_customers
WHERE d_id='A00007' 
GROUP BY d_id, d_name;

To get data for all d_id values, simply remove the WHERE clause.

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your query.

select sum(case when coalesce(c_email, '') != '' and d_id='A00007' then 1 end )*100.00/
        count(c_number) else 0 end as percentage
        , sum(case when d_id='A00007' then 1 else 0 end) as count_1
        , sum(case when d_id='A00007' and coalesce(c_email, '') !='' then 1 else 0 end) as count_2
        , d_id, d_name
from distinct_customers
group by d_id, d_name;

If you just want to merge the results. use union all.

select sum(case when coalesce(c_email, '') != '' and d_id='A00007' then 1 end )*100.00/
            count(c_number) else 0 end as percentage           
            , d_id, d_name
    from distinct_customers
    group by d_id, d_name
union all
select sum(case when d_id='A00007' then 1 else 0 end) as count_1          
            , d_id, d_name
    from distinct_customers
    group by d_id, d_name
union all
select sum(case when d_id='A00007' and coalesce(c_email, '') !='' then 1 else 0 end) as count_2
            , d_id, d_name
    from distinct_customers
    group by d_id, d_name;

Upvotes: 0

Related Questions