Onik92 Onik92 - 7 months ago 9
SQL Question

mysql/php merging/combining row values

I am having a problem with an select statement. When im using distinct it will still give me duplicate of certain rows. I have tried using group by it fixed the duplication but it deleted one of the values.

Links to Image:
Group by and Distinct

this is what i want it to output:
Solution

this is my code:

SELECT DISTINCT M.Major,
S.Ethnicity = 'American Indian or Alaska Native' AS AIAN,
S.Ethnicity = 'Asian' AS Asian,
S.Ethnicity = 'Black or African American' AS Black,
S.Ethnicity = 'Hispanic/Latino' AS Hisp,
S.Ethnicity = 'Native Hawaiian or Other Pacific Islander' AS Native,
S.Ethnicity = 'White' AS White,
S.Ethnicity = 'Unknown/Other' As Other
FROM STUDENT AS S, MAJOR AS M
WHERE S.Major_ID = M.MajorID and S.Gender = 'Female'
Group By M.Major;

STUDENT |
CREATE TABLE `STUDENT` (
`StudentID` int(11) NOT NULL AUTO_INCREMENT,
`FirstName` char(35) NOT NULL,
`LastName` char(35) NOT NULL,
`DateOfBirth` date NOT NULL,
`SSN` char(11) NOT NULL,
`GGCEmail` varchar(100) NOT NULL,
`Gender` char(6) NOT NULL,
`Ethnicity` char(100) NOT NULL,
`IsGrad` char(3) NOT NULL,
`Major_ID` int(11) NOT NULL,
`Minor_ID` int(11) DEFAULT NULL,
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`StudentID`),
KEY `MAJOR_STUDENT_FK` (`Major_ID`),
KEY `MINOR_STUDENT_FK` (`Minor_ID`),
CONSTRAINT `MAJOR_STUDENT_FK` FOREIGN KEY (`Major_ID`) REFERENCES `MAJOR` (`MajorID`),
CONSTRAINT `MINOR_STUDENT_FK` FOREIGN KEY (`Minor_ID`) REFERENCES `MINOR` (`MinorID`)

MAJOR |
CREATE TABLE `MAJOR` (
`MajorID` int(11) NOT NULL AUTO_INCREMENT,
`Major` char(100) NOT NULL,
`EntryGPA` decimal(3,2) NOT NULL,
PRIMARY KEY (`MajorID`)

Answer

I think you should use a sum and select case

    SELECT DISTINCT M.Major, 
        sum( case S.Ethnicity when 'American Indian or Alaska Native' then 1 else 0 END)  AS AIAN,
        sum( case S.Ethnicity when 'Asian' then 1 else 0 END ) AS Asian,
        sum( case S.Ethnicity when 'Black or African American' then 1 else 0 END ) AS Black,
        sum( case S.Ethnicity when 'Hispanic/Latino' then 1 else 0 END) AS Hisp,
        sum( case S.Ethnicity when 'Native Hawaiian or Other Pacific Islander' then 1 else 0 END) AS Native,
        sum( case S.Ethnicity when 'White' then 1 else 0 END) AS White,
        sum( case S.Ethnicity when 'Unknown/Other' then 1 else 0 END) As Other
    FROM STUDENT AS S, MAJOR AS M
    WHERE S.Major_ID = M.MajorID and S.Gender = 'Female'
    Group By M.Major;
Comments