need a SQL

after select put a Distinct

select Distinct 'continue the same query'

Select allOpen in new window

Can you post all the distinct ethic origins that your system tracks (A, C, etc.)?

A simple
SELECT DISTINCT(ethnicorg) FROM EmpCstFV
would do it.

try this, if you need add one group to considere only add the field in the partition by

select Distinct
empGCstF5 as Location, 
EmpgGCstF1 as Title, 
Count(sex) over (Partition By ethnicorg) as Sex, 
Count (ethnicorg) over (Partition By ethnicorg) as ethnicorg
from dbo.EmpCstFv inner join Employee on EmpCstFv.EmpUID = Employee.EmpuID
Where EmpCstFv.StartDate >= '8/1/2011' and EmpCstFv.StartDate <= '7/31/2012'
and EmpCstFv.CompanyID = 15 and EmpGCstF5 = 'BCS/TCS Philadelphia, PA'
and EmpGCstF1 = 'Harrassment Prevention'
order by EmpGCstF5, EmpGCstF1

Select allOpen in new window

Crashman - Your SQL gives me 2 rows, I think because there is one for each sex based on the grouping.

For each location/title, I want only 1 row.    I need a column the row that gives the number of White Males, White Females, Black Males, Black Females, etc.

sl8rz.  Yes,

B = African American
C = White
N = Native American
S = Asian
H = Hispanic
R = Mixed Race.

the query return two rows because exist two different values in ETHNICORG.

based on your comment change the partition by

select Distinct
empGCstF5 as Location, 
EmpgGCstF1 as Title, 
Count(sex) over (Partition By empGCstF5,EmpgGCstF1 ) as Sex, 
Count (ethnicorg) over (Partition By empGCstF5,EmpgGCstF1 ) as ethnicorg
from dbo.EmpCstFv inner join Employee on EmpCstFv.EmpUID = Employee.EmpuID
Where EmpCstFv.StartDate >= '8/1/2011' and EmpCstFv.StartDate <= '7/31/2012'
and EmpCstFv.CompanyID = 15 and EmpGCstF5 = 'BCS/TCS Philadelphia, PA'
and EmpGCstF1 = 'Harrassment Prevention'
order by EmpGCstF5, EmpGCstF1
                                            

Select allOpen in new window

OK,  I might have been unclear.   I these need columns on a Location/title basis, one row per Location/title:

Location
Title
Count of White Females
Count of White Males
Count of African American Females
Count of African American Males
Count of Hispanic Females
Count of Hispanic Males
Count of Asian Females
Count of Asian Males
Count of Native American Females
Count of  Native American Males
Count of Mixed Race Females
Count of Mixed Race Males

In Access, I would simply concatenate the two fields together

[Sex] & '/' & [EthnicOrg]

and pivot on that combination, can you not do the same in SQL Server?

This is one way you could do it (another way would be to use a PIVOT operator):

SELECT  EmpGCstF5 AS Location,
        EmpGCstF1 AS Title,
        SUM(CASE WHEN sex = 'M'
                      AND ethnicorg = 'B' THEN 1
                 ELSE 0
            END),
        SUM(CASE WHEN sex = 'M'
                      AND ethnicorg = 'C' THEN 1
                 ELSE 0
            END),
        SUM(CASE WHEN sex = 'M'
                      AND ethnicorg = 'N' THEN 1
                 ELSE 0
            END),
        SUM(CASE WHEN sex = 'M'
                      AND ethnicorg = 'S' THEN 1
                 ELSE 0
            END),
        SUM(CASE WHEN sex = 'M'
                      AND ethnicorg = 'H' THEN 1
                 ELSE 0
            END),
        SUM(CASE WHEN sex = 'M'
                      AND ethnicorg = 'R' THEN 1
                 ELSE 0
            END),
        SUM(CASE WHEN sex = 'F'
                      AND ethnicorg = 'B' THEN 1
                 ELSE 0
            END),
        SUM(CASE WHEN sex = 'F'
                      AND ethnicorg = 'C' THEN 1
                 ELSE 0
            END),
        SUM(CASE WHEN sex = 'F'
                      AND ethnicorg = 'N' THEN 1
                 ELSE 0
            END),
        SUM(CASE WHEN sex = 'F'
                      AND ethnicorg = 'S' THEN 1
                 ELSE 0
            END),
        SUM(CASE WHEN sex = 'F'
                      AND ethnicorg = 'H' THEN 1
                 ELSE 0
            END),
        SUM(CASE WHEN sex = 'F'
                      AND ethnicorg = 'R' THEN 1
                 ELSE 0
            END)
FROM    dbo.EmpCstFV c
        INNER JOIN dbo.Employee e ON c.EmpUID = e.EmpUID
WHERE   c.StartDate BETWEEN '20120731' AND '20110801'
        AND c.CompanyID = 15
        AND EmpGCstF5 = 'BCS/TCS Philadelphia, PA'
        AND EmpGCstF1 = 'Harrassment Prevention'
GROUP BY EmpGCstF5,
        EmpGCstF1

Select allOpen in new window

Oops, I seem to have duplicated a solution.  I am sorry about that.

Thanks so much!

>>See the attached image.   I need a modified query that will give me 1 row instead of 3.  I need counts on gender/ethnic basis.<<
Given your example, what would your one row look like?