SQL Query to give percentage of colour customers

rgss
rgss used Ask the Experts™
on
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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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

Author

Commented:
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."
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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

Author

Commented:
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
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
That only grouped them by sale (regardless of sale items).
This groups by sales person
select tot.stylistname,
  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

Open in new window

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial