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.

cyberkiwi

select salehdr.stylistname,
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

cyberkiwi

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.stylistname and tot.salehdrID=col.salehdrID

cyberkiwi

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

Open in new window

rgss

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."

cyberkiwi

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

Open in new window

rgss

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

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgss

ASKER

Thanks cyberkiwi, that worked! You have saved me a lot of time and effort, greatly appreciated.