Dave KIlby
asked on
SQL Query pull values for list
I want to pull data from a table, but I want to pull all the reasons and show 0 if there is no entry for the day.
This what I want the data to look like
When I run this query, I only get Not Interested and Invalid Contact, is there a way to pull all the reasons and pull the number, 0 if null
I dont want to hardcode the reasons, as i want to include any new ones that might be added, so if I run this query it pulls all possible entries, and this list is what i want to utilize for the report.
Thanks
This what I want the data to look like
When I run this query, I only get Not Interested and Invalid Contact, is there a way to pull all the reasons and pull the number, 0 if null
SELECT Reason, Count(ID) from t_customers
where createddate = getDate()
group by Reason
I dont want to hardcode the reasons, as i want to include any new ones that might be added, so if I run this query it pulls all possible entries, and this list is what i want to utilize for the report.
select reason from t_customers
group by reason
Thanks
ASKER
For some reason this is working - when i run the line SELECT Reason FROM dbo.t_customers GROUP BY Reason i get all the reasons, but when i run the full code i only get the reasons that meet the Created Date - which is only the 2 reasons.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT Reason FROM dbo.t_customers GROUP BY Reason
)
SELECT cr.Reason, COUNT(*) AS Reason_Count
FROM cte_reasons cr
LEFT OUTER JOIN dbo.t_customers tc ON tc.Reason = cr.Reason
WHERE tc.createddate = GETDATE()
GROUP BY cr.Reason