SQL Problem

[md092686]

I am trying to compute Total Employees that are White, Black, Indian, etc by an ORG Code. The ORG Code for Admin would be 0010. I have attached an example of what I am trying to do. I have 1 Black Male and 3 White Males and 3 White Females. I need a Total Field to give me under Total Employees 6 Whites and 1 Black. I have the View just about done, but I just do not know how to make the computer count TotalEmp. See Example:

My Coding: SELECT HORGCD, COUNT(*) AS TotalEmp, [long-name], SEX
FROM dbo.['ABC TOTALS BY RACE AND ORG WITH$']
GROUP BY HORGCD, [long-name], SEX

Any help would be appreciated.

md092686

 

[bborissov]

SELECT Tbl1.HORGCD, 
       COUNT(*) AS TotalEmp,
       [long-name],
       Tbl1.SEX,
       MAX(Tbl2.TotalEmpl) AS TotalEmpl
FROM dbo.['ABC TOTALS BY RACE AND ORG WITH$'] Tbl1
INNER JOIN (SELECT HORGCD,
                   COUNT(*) AS TotalEmpl
            FROM dbo.['ABC TOTALS BY RACE AND ORG WITH$']
            GROUP BY HORGCD) Tbl2
ON Tbl1.HORGCD = Tbl2.HORGCD
GROUP BY Tbl1.HORGCD, Tbl1.[long-name], Tbl1.SEX

(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[md092686]

Boris,

This Coding gives the following:

HORGCD TotalEmp long-name Sex TotalEmp1
0010 1 Black/Afr. Amer. M 7
0010 3 White F 7
0010 3 White M 7
0020 1 Black/Afr. Amer. M 12
0020 9 White F 12

I need for TotalEmp to appear as: 0010 1 Black/afr
0010 6 White

In other words, we have by 0010 1 Black Male and 6 White Males and Females. The reason I need the total like this is because I will be doing a claculation to determine the percentage of TotalEmp that are Black (M & F) and White (M & F) and so on. 6 Whites/7 = 85.71% White working.

Does this make sense?

You are very close! I came up with this result too.

 

[bborissov]

You need a Total Employees?
Can you post some simple data and what you want from it?


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[md092686]

Boris,

The query you made from the Tables is:


HORGCD TotalEmp RACE SEX TotalEmp1

0010 1 Black Male 7
0010 3 White Male 7
0010 3 White Female 7

The TotalEmpl you have is 7. This is correct

But I need to take this same query or view and make another one which should appear as:

HORGCD TotalEmp RACE SEX TotalEmp1

0010 1 Black Male 7
0010 6 White Male & Female) 7
The column TotalEmp with 1 Black Male and 6 White Male and Female Employees. The query you did above contains 3 White Males and 3 White Females. I want a Total for White Male and White Female which totals 6.

 

[bborissov]

Then do not GROUP by SEX. Put a constant string there "Male and/or Female"

SELECT Tbl1.HORGCD,
       COUNT(*) AS TotalEmp,
       Race,
       "Male and/or Female" AS Sex,
       MAX(Tbl2.TotalEmpl) AS TotalEmpl
FROM dbo.['ABC TOTALS BY RACE AND ORG WITH$'] Tbl1
INNER JOIN (SELECT HORGCD,
                   COUNT(*) AS TotalEmpl
            FROM dbo.['ABC TOTALS BY RACE AND ORG WITH$']
            GROUP BY HORGCD) Tbl2
ON Tbl1.HORGCD = Tbl2.HORGCD
GROUP BY Tbl1.HORGCD, Tbl1.Race

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[md092686]

Boris,

When I run the view I get an error message, [male and/or Female] is an invalid field name.

 

[wbodger]

You would have to add SEX to the view, but in your calculations you don't care what sex they are, right? Or are you going to do a more complete demographic breakdown? If you just group by Race you should be able to Sum or count what you want. What is the exact structure you are pulling data from?

Willie

 

[md092686]

Willie,

Yes, I need to know how many males and females of each race that I have by DHORGCD.

I have got the Query to work, but I do not know how to place the SEX in there.

Look at this query and see what I need to do.

SELECT Tbl1.HORGCD, COUNT(*) AS TotalEmp, Tbl1.RACE, MAX(Tbl2.TotalEmpl) AS TotalEmpl, Tbl1.[long-name] AS RaceName
FROM dbo.['ABC TOTALS BY RACE AND ORG WITH$'] AS Tbl1
INNER JOIN
(SELECT HORGCD, COUNT(*) AS TotalEmpl
FROM dbo.['ABC TOTALS BY RACE AND ORG WITH$']
GROUP BY HORGCD) AS Tbl2 ON Tbl1.HORGCD = Tbl2.HORGCD
GROUP BY Tbl1.HORGCD, Tbl1.RACE, Tbl1.[long-name]

When you place SEX in the view, it causes my totals to breakout again with 1 Black Male with 3 White Male and 3 White Females. I want this breakout, but I want my totals by race to stay there with 6 Whites and 1 Black on 0010 Org

 

[bborissov]

Sorry, there must be a single quotes not double ones:

SELECT Tbl1.HORGCD,
       COUNT(*) AS TotalEmp,
       Race,
       'Male and/or Female' AS Sex,
       MAX(Tbl2.TotalEmpl) AS TotalEmpl
FROM dbo.['ABC TOTALS BY RACE AND ORG WITH$'] Tbl1
INNER JOIN (SELECT HORGCD,
                   COUNT(*) AS TotalEmpl
            FROM dbo.['ABC TOTALS BY RACE AND ORG WITH$']
            GROUP BY HORGCD) Tbl2
ON Tbl1.HORGCD = Tbl2.HORGCD
GROUP BY Tbl1.HORGCD, Tbl1.Race

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[md092686]

Boris,

We are very close. I need for a total to occur on Whites by HORGCD, Blacks By HORGCD, HISPANIC BY HORGCD, American Indian by HORGCD, ASIAN by HORGCD, and so on. So I need a column to appear with for example on 0010 HORGCD there should be 6 Whites which are composed of 3 White Females and 3 White Males and 1 Black Male.

HORGCD TotalEmp EMPLOYEES GENDER RACE
0010 6 3 Female White
0010 3 Male White
0010 1 1 Male Black


The above is what I want the Query to pull and show. The data should appear all of the way down through the Query for each Race, But I want the TotalEMP to show only Distinctly.

 

[md092686]

Boris,

I believe I can get my results with your query once I learn how to develop my percentages from your TotalEMp and TotalEMPL. Is it possible to do a calculation with the TotalEMP and TotalEmpl? Like TotalEMP/TotalEmpl AS Percentage Race. How would I word this in the coding? When I Try to use AVG(TotalEMP/TotalEMPL) AS Percentage. I get an SQL error.

 

[bborissov]

SELECT Tbl1.HORGCD,
       COUNT(*) AS TotalEmp,
       Race,
       'Male and/or Female' AS Sex,
       MAX(Tbl2.TotalEmpl) AS TotalEmpl,
       COUNT(*)/MAX(Tbl2.TotalEmpl)*100 AS Percentage
FROM dbo.['ABC TOTALS BY RACE AND ORG WITH$'] Tbl1
INNER JOIN (SELECT HORGCD,
                   COUNT(*) AS TotalEmpl
            FROM dbo.['ABC TOTALS BY RACE AND ORG WITH$']
            GROUP BY HORGCD) Tbl2
ON Tbl1.HORGCD = Tbl2.HORGCD
GROUP BY Tbl1.HORGCD, Tbl1.Race

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP