rgss
asked on
SQL Query to give percentage of colour customers
I need to create a query to return the number and percentage of total customers that had a colour service in a POS database for a hairdressing salon. The data is in the sale header table: salehdr and the sale lines table: saleline. They are linked by the salehdrID field, with a one to many relationship. The colour services are saleline.categoryID=3. There may be more than one saleline record with categoryID=3 for each salehdr record but in that case it needs to count as 1, not the number of saleline records. Also, any sales that have no services: only catedoryID = 7 shouldn't be included at all.
The results need to be grouped by salehdr.stylistname, to give count and percentage of total for each stylist, as well as for overall. This can be in the same query or two seperate queries.
I need to be able to filter by salehdr.saledate to give results for a lingle day or a range.
I have only basic SQL knowledge so I'm hoping there is an expert that can help.
The results need to be grouped by salehdr.stylistname, to give count and percentage of total for each stylist, as well as for overall. This can be in the same query or two seperate queries.
I need to be able to filter by salehdr.saledate to give results for a lingle day or a range.
I have only basic SQL knowledge so I'm hoping there is an expert that can help.
Access version..
select col.stylistname, col.salehdrID,
COUNT(col.salehdrID) as CountColour,
COUNT(tot.salehdrID) as CountTotal,
1.0 * COUNT(col.salehdrID) / COUNT(tot.salehdrID) as PercentageColour
from (
select distinct h2.stylistname, h2.salehdrID
from salehdr h2
inner join saleline l2 on l2.salehdrID = h2.salehdrID
where l1.categoryID<>7
) tot left join (
select distinct h1.stylistname, h1.salehdrID
from salehdr h1
inner join saleline l1 on l1.salehdrID = h1.salehdrID
where l1.categoryID=3) col on tot.stylistname=col.stylis tname and tot.salehdrID=col.salehdrI D
select col.stylistname, col.salehdrID,
COUNT(col.salehdrID) as CountColour,
COUNT(tot.salehdrID) as CountTotal,
1.0 * COUNT(col.salehdrID) / COUNT(tot.salehdrID) as PercentageColour
from (
select distinct h2.stylistname, h2.salehdrID
from salehdr h2
inner join saleline l2 on l2.salehdrID = h2.salehdrID
where l1.categoryID<>7
) tot left join (
select distinct h1.stylistname, h1.salehdrID
from salehdr h1
inner join saleline l1 on l1.salehdrID = h1.salehdrID
where l1.categoryID=3) col on tot.stylistname=col.stylis
MS Access with a date range filter
select col.stylistname, col.salehdrID,
COUNT(col.salehdrID) as CountColour,
COUNT(tot.salehdrID) as CountTotal,
1.0 * COUNT(col.salehdrID) / COUNT(tot.salehdrID) as PercentageColour
from (
select distinct h2.stylistname, h2.salehdrID
from salehdr h2
inner join saleline l2 on l2.salehdrID = h2.salehdrID
where l2.categoryID<>7
and h2.saledate between [Report Start] and [Report End]
) tot left join (
select distinct h1.stylistname, h1.salehdrID
from salehdr h1
inner join saleline l1 on l1.salehdrID = h1.salehdrID
where l1.categoryID=3
and h1.saledate between [Report Start] and [Report End]
) col on tot.stylistname=col.stylistname and tot.salehdrID=col.salehdrID
ASKER
Thanks cyberkiwi.
When I try to run your 2nd or 3rd query i get an error saying:
"You tried to execute a query that does not include the specified expression 'stylistname' as part of an aggregate function."
When I try to run your 2nd or 3rd query i get an error saying:
"You tried to execute a query that does not include the specified expression 'stylistname' as part of an aggregate function."
I am sorry. Please find the revised script below.
select tot.stylistname, tot.salehdrID,
COUNT(col.salehdrID) as CountColour,
COUNT(tot.salehdrID) as CountTotal,
1.0 * COUNT(col.salehdrID) / COUNT(tot.salehdrID) as PercentageColour
from (
select distinct h2.stylistname, h2.salehdrID
from salehdr h2
inner join saleline l2 on l2.salehdrID = h2.salehdrID
where l2.categoryID<>7
and h2.saledate between [Report Start] and [Report End]
) tot left join (
select distinct h1.stylistname, h1.salehdrID
from salehdr h1
inner join saleline l1 on l1.salehdrID = h1.salehdrID
where l1.categoryID=3
and h1.saledate between [Report Start] and [Report End]
) col on tot.stylistname=col.stylistname and tot.salehdrID=col.salehdrID
Group by tot.stylistname, tot.salehdrID
ASKER
When I run the query i get:
stylistname salehdrID CountColour CountTotal PercentageColour
Ero 739 0 1 0
Ero 741 1 1 1
Ero 744 1 1 1
Ilian 736 0 1 0
Ilian 738 0 1 0
Ilian 743 0 1 0
Julie 737 1 1 1
Julie 740 0 1 0
but what I need to see is that Ero 2 of 3, Illian 0 of 3 and Julie 1 of 2
stylistname salehdrID CountColour CountTotal PercentageColour
Ero 739 0 1 0
Ero 741 1 1 1
Ero 744 1 1 1
Ilian 736 0 1 0
Ilian 738 0 1 0
Ilian 743 0 1 0
Julie 737 1 1 1
Julie 740 0 1 0
but what I need to see is that Ero 2 of 3, Illian 0 of 3 and Julie 1 of 2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks cyberkiwi, that worked! You have saved me a lot of time and effort, greatly appreciated.
count(distinct case when saleline.categoryID=3 then salehdr.salehdrID end),
count(distinct case when saleline.categoryID=3 then salehdr.salehdrID end) / count(distinct salehdr.salehdrID)
from salehdr
inner join saleline on saleline.salehdrID = salehdr.salehdrID
where saleline.categoryID<>7
and salehdr.saledate >= [report start date] and salehdr.saledate <= [report end date]
group by salehdr.stylistname, salehdr.salehdrID