Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

need a SQL

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.  

So, I would need one row that gives me columns that indicate the number of males and females for each ethnic code.
sql.png
0
HLRosenberger
Asked:
HLRosenberger
  • 4
  • 3
  • 2
  • +4
1 Solution
 
CrashmanCommented:
after select put a Distinct

select Distinct 'continue the same query'

Open in new window

0
 
David L. HansenProgrammer AnalystCommented:
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.
0
 
CrashmanCommented:
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

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
HLRosenbergerAuthor Commented:
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.
0
 
HLRosenbergerAuthor Commented:
sl8rz.  Yes,

B = African American
C = White
N = Native American
S = Asian
H = Hispanic
R = Mixed Race.
0
 
CrashmanCommented:
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
                                            

Open in new window

0
 
HLRosenbergerAuthor Commented:
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
0
 
Dale FyeCommented:
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?
0
 
LIONKINGCommented:
Try something like this. Maybe this will give you the correct results:

SELECT
EmpGCstF5 as Location, 
EmpgGCstF1 as Title, 
SUM(CASE WHEN sex='M' AND ethnicorg='B' THEN 1 ELSE 0 END) [Male African American],
SUM(CASE WHEN sex='F' AND ethnicorg='B' THEN 1 ELSE 0 END) [Female African American],
SUM(CASE WHEN sex='M' AND ethnicorg='C' THEN 1 ELSE 0 END) [Male White],
SUM(CASE WHEN sex='F' AND ethnicorg='C' THEN 1 ELSE 0 END) [Female White],
SUM(CASE WHEN sex='M' AND ethnicorg='N' THEN 1 ELSE 0 END) [Male Native American],
SUM(CASE WHEN sex='F' AND ethnicorg='N' THEN 1 ELSE 0 END) [Female Native American],
SUM(CASE WHEN sex='M' AND ethnicorg='S' THEN 1 ELSE 0 END) [Male Asian],
SUM(CASE WHEN sex='F' AND ethnicorg='S' THEN 1 ELSE 0 END) [Female Asian],
SUM(CASE WHEN sex='M' AND ethnicorg='H' THEN 1 ELSE 0 END) [Male Hispanic],
SUM(CASE WHEN sex='F' AND ethnicorg='H' THEN 1 ELSE 0 END) [Female Hispanic],
SUM(CASE WHEN sex='M' AND ethnicorg='R' THEN 1 ELSE 0 END) [Male Mixed Race],
SUM(CASE WHEN sex='F' AND ethnicorg='R' THEN 1 ELSE 0 END) [Female Mixed Race]
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'
GROUP BY EmpGCstF5, EmpGCstF1
ORDER BY EmpGCstF5, EmpGCstF1

Open in new window

0
 
Anthony PerkinsCommented:
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

Open in new window

0
 
Anthony PerkinsCommented:
Oops, I seem to have duplicated a solution.  I am sorry about that.
0
 
HLRosenbergerAuthor Commented:
Thanks so much!
0
 
awking00Commented:
>>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?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now