I have a SQL query that returns data which includes quote types along with customer information. There are 4 types of quotes (Open, Dead, Requote, Project). I want to be able to count each type for each customer. And also count the total. I have not found a way to accomplish this.
Ultimately I want this to go into a gauge on an SSRS report so that we can tell how many of the quotes (percentage) eventually turn into a project.
I haven't found anything in my searches that works. Thanks in advance for any advice.
Using CTE's
WITH quoteTotal as (
Select customer, count(*) as customer_total
from customer
group by customer
),
typeQuote as (
Select customer, quote_type, count(*) as quote_total
from customer
group by customer, quote_type
)
SELECT T.customer, T.quote_type, T.quote_total, Q.customer_total
FROM typeQuote T
INNER JOIN quoteTotal Q
ON T.customer = Q.customer
I think using window functions can be easy.
SELECT DISTINCT
customer,
quote_type,
COUNT(*) OVER (partition by customer, quote_type order by customer) as type_total,
COUNT(*) OVER (partition by customer order by customer) as customer_total
FROM customers
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments