It's too tricky for a UNION because the total I need at the bottom can only be achieved with a higher level grouping
DDL
create table LeadTracker
(property_name varchar(30), lead_id int);
insert into LeadTracker values
('Property1', 18709),
('Property1', 18323),
('Property1', 19547),
('Property2', 18709),
('Property2', 19015),
('Property2', 18323),
('Property2', 19547),
('Property3', 19015),
('Property3', 18323),
('Property3', 19547),
('Property4', 19015),
('Property4', 19547);
--first query shows how many leads were sent to each property and totals it at the bottom: 12 leads total.
select
property_name,
count(distinct lead_id)
from leadtracker
group by property_name;
--second query shows that only 4 were unique leads. I need for this 'unique lead' total to appear beneath the total leads.
select
count(distinct lead_id)
from LeadTracker;
I need the results to look like this, all in single column
select
property_name,
count(distinct lead_id) as leads_sent
from leadtracker
group by property_name
union all
Select property_name = 'Total', leads_sent = 12;
PLUS
Select count(distinct lead_id) 'TOTAL UNIQUE leads only'
from LeadTracker;
please look at image file I attached. How can I do this in SSRS?