Grouping with formula
-
tblmystudent
records | course | course_level | major | male | female
------------------------------------------------------------------------
I | English | Cert | Languages | 10 | 34
I | Child Care | Diploma | Social Sc | 45 | 12
I | Human Think | Diploma | Social Sc | 2 | 23
E | Psychology | Diploma | Social Sc | 56 | 23
E | Counselling | Diploma | Social Sc | 23 | 12
G | Motivation | Diploma | Social Sc | 2 | 12
G | Brain Dev | Diploma | Social Sc | 3 | 9
E | France | Cert | Languages | 5 | 7
I | Adv English | Diploma | Languages | 7 | 4
How to group by major?
The formula is,
Registered_Student = (I + E) - G
The expected result shown as follow
major | cert_male | cert_female| diploma_male | diploma_female
--------------------------------------------------------------------------
Languages | 15 | 41 | 7 | 4
Social Sc | 0 | 0 | 121 | 49
please help me
-
You're looking for a Pivot function... for SQL 2000 there are alot of posts here, do a search for Pivot script on this site... same for SQL 2005 which has a built in Pivot Funtion.
-
-
I don't get how you are coming up with your numbers......for example:
Languages, there are three language courses which have a total of 22 Males, but you are only counting two language classes. How do you determine which to use?
And what is the purpose of the 'formula'? You can't add and subtract letters (I+E)-G won't work.
-SQLBill
-
Bill - the #s are summed based on course_level of "cert" or "diploma"..
the formula is not adding/subtracting letters but the #s in the columns associated with them..however, I still don't understand the formula itself...
**ASCII stupid question, get a stupid ANSI !!!** -
Try this:
declare @tblmystudent table(records char(1),course varchar(20),course_level varchar(10),major varchar(20),male int,female int)
insert @tblmystudent values('I','English','Cert','Languages','10','34')
insert @tblmystudent values('I','ChildCare','Diploma','SocialSc','45','12')
insert @tblmystudent values('I','HumanThink','Diploma','SocialSc','2','23')
insert @tblmystudent values('E','Psychology','Diploma','SocialSc','56','23')
insert @tblmystudent values('E','Counselling','Diploma','SocialSc','23','12')
insert @tblmystudent values('G','Motivation','Diploma','SocialSc','2','12')
insert @tblmystudent values('G','BrainDev','Diploma','SocialSc','3','9')
insert @tblmystudent values('E','France','Cert','Languages','5','7')
insert @tblmystudent values('I','AdvEnglish','Diploma','Languages','7','4')
select major
, sum(case when course_level = 'cert' then case when records = 'G' then -1 * male else male end end) as cert_male
, sum(case when course_level = 'cert' then case when records = 'G' then -1 * female else female end end) as cert_female
, sum(case when course_level = 'Diploma' then case when records = 'G' then -1 * male else male end end) as diploma_male
, sum(case when course_level = 'Diploma' then case when records = 'G' then -1 * female else female end end) as diploma_female
from @tblmystudent
group by major
-
I agree with you, but that doesn't explain the results which was my point. There are three language classes with a total of 22 Males. The three language classes have two I's and one E. That's 22, not 15.
-SQLBill
-
Bill,
15 are of level "cert" and 7 are of level "diploma" which are broken out into separate columns.
-
I was trying an approach that I hoped would be more flexible since I am assuming this is test data. I ran into an odd problem I cannot figure out.
DECLARE @Student TABLE( Records char(1),
Course varchar(15),
Course_Level varchar(15),
Major varchar(15),
Male integer,
Female integer)
INSERT INTO @Student
SELECT 'I', 'English', 'Certification', 'Languages', 10, 34 UNION ALL
SELECT 'I', 'Child Care', 'Diploma', 'Social Science', 45, 12 UNION ALL
SELECT 'I', 'Human Think', 'Diploma', 'Social Science', 2, 23 UNION ALL
SELECT 'E', 'Psychology', 'Diploma', 'Social Science', 56, 23 UNION ALL
SELECT 'E', 'Counselling', 'Diploma', 'Social Science', 23, 12 UNION ALL
SELECT 'G', 'Motivation', 'Diploma', 'Social Science', 2, 12 UNION ALL
SELECT 'G', 'Brain Dev', 'Diploma', 'Social Science', 3, 9 UNION ALL
SELECT 'E', 'France', 'Certification', 'Languages', 5, 7 UNION ALL
SELECT 'I','Adv English', 'Diploma', 'Languages', 7, 4
DECLARE @OutPut TABLE( Major varchar(15),
Cert_Male integer,
Cert_Female integer,
Diploma_Male integer,
Diploma_Female integer)
INSERT INTO @OutPut( Major)
SELECT DISTINCT Major FROM @Student
UPDATE @OutPut SET
Cert_Male = CASE
WHEN S.Course_Level = 'Certification'
THEN S.Male
END,
Cert_Female = CASE
WHEN S.Course_Level = 'Certification'
THEN S.Female
END,
Diploma_Male = CASE
WHEN S.Course_Level = 'Diploma'
THEN S.Male
END,
Diploma_Female = CASE
WHEN S.Course_Level = 'Diploma'
THEN S.Female
END
FROM @OutPut O
INNER JOIN( SELECT Major, Course_Level,
SUM( Male) AS Male, SUM( Female) AS Female
FROM @Student
GROUP BY Major, Course_Level) S ON( O.Major = S.Major)
SELECT Major,
ISNULL( Cert_Male, 0) AS Cert_Male,
ISNULL( Cert_Female, 0) AS Cert_Female,
ISNULL( Diploma_Male, 0) AS Diploma_Male,
ISNULL( Diploma_Female, 0) AS Diploma_Female
FROM @OutPut
ORDER BY Major
Major Cert_Male Cert_Female Diploma_Male Diploma_Female
--------------- ----------- ----------- ------------ --------------
Languages 0.00 0.00 7.00 4.00
Social Science 0.00 0.00 131.00 91.00
But the subselect yields:
SELECT Major, Course_Level,
SUM( Male) AS Male, SUM( Female) AS Female
FROM @Student
GROUP BY Major, Course_Level
Major Course_Level Male Female
--------------- --------------- ----------- -----------
Languages Certification 15.00 41.00
Languages Diploma 7.00 4.00
Social Science Diploma 131.00 91.00
Why are my Cert_Male and Cert_Female empty? [Obviously I had not dealt with the "G" records yet...]
I wasn't born stupid - I had to study.
-
Becuase when the second row of the subquery is being processed, the CERT* columns are being set to null because the case is returning null for them.
Check out the following query:
SELECT
CASE
WHEN S.Course_Level = 'Certification'
THEN S.Male
Else Cert_male
END Cert_Male,
CASE
WHEN S.Course_Level = 'Certification'
THEN S.Female
Else Cert_female
END Cert_Female,
CASE
WHEN S.Course_Level = 'Diploma'
THEN S.Male
Else Diploma_Male
END Diploma_Male,
CASE
WHEN S.Course_Level = 'Diploma'
THEN S.Female
else Diploma_Female
END Diploma_Female, s.*
FROM @OutPut O
INNER JOIN( SELECT Major, Course_Level,
SUM( Male) AS Male, SUM( Female) AS Female
FROM @Student
GROUP BY Major, Course_Level) S ON( O.Major = S.Major)
Also, what is more flexible about your approach? It looks much more complicated and requires many more joins to the Student table.
-
Sorry, but that did not fix it.
I was planning on making the Course_Level a selection from the data rather than a "hardcode"... Hence, my idea of flexibility. May not have worked... oil well..
I wasn't born stupid - I had to study.
-
The query wasn't to fix it, it was to point out the problem. The query produces the following output:
Cert_Male Cert_Female Diploma_Male Diploma_Female Major Course_Level Male Female
15 41 NULL NULL Languages Certification 15 41
NULL NULL 7 4 Languages Diploma 7 4
NULL NULL 131 91 Social Science Diploma 131 91
After the first update to @Ouput, the values for the "Languages" row are OK. When the second row of the set is applied, Cert* column values are overwritten with the NULL values.
-
I don't understand what you are saying...
If I try this with a RIGHT JOIN, it updates the Certificate fields, but not the Languages- Diploma fields... What is being overwritten there?
I wasn't born stupid - I had to study.
-
First, the else statements that I added don't work, they only ever return the initial state of the @Output table.
After the first "Languages" row is applied to the @OutPut table and all columns are updated, the data looks like
Languages - 15 - 41 - NULL - NULL
Now the second row is applied and the same row in the @OutPut table is updated and all columns are reset to the values of the CASE statement, the data looks like:
Languages - NULL - NULL - 7 - 4
This is because for that row, the values of Cert_Male and Cert_Female are NULL
Check out the following example:
--create a 2 tables
declare @table1 table (rowid int, val1 int, val2 int)
insert @table1 values (1, 1, 1)
insert @table1 values (1, 2, 2)
declare @table2 table (rowid int, total int)
insert @table2 values (1, NULL)
--update the total to the sum of the 2 vales in table1
update t2
set t2.total = t1.val1 + t1.val2
from @table2 t2
inner join @table1 t1
on t1.rowid = t2.rowid
--What do you expect the answer to be? 2? 4? 6?
select * from @table2
--You will get either 2 or 4 depending on which row the server applies second
--create another table
declare @table3 table (rowid int, val1 int, val2 int)
insert @table3 values (1, NULL, NULL)
update t3
set t3.val1 = t1.val1
,t3.val2 = t1.val2
from @table3 t3
inner join @table1 t1
on t1.rowid = t3.rowid
--again the values will be either 1-1-1 or 1-2-2 depending on which row is applied second
select * from @table3
-
This is sooo simple and my head was just not getting around it! Thank you!!!
(I am still playing with this so no hardcoding is needed as I have run across so many instances of this kind of query and they promise "No further types will be included" and six months later they add one or two and you cannot remember all of the SP's that depend upon that...)
Thanks for sticking out my brain f#rt...
I wasn't born stupid - I had to study.
-
tq everyone. especially mr. JeffB. your solution give me an idea to solve it.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply