May 19, 2011 at 5:42 am
I have following table structure:
CREATE TABLE [dbo].[Results](
[Id] [int] IDENTITY(1,1) NOT NULL,
[StudentId] [int] NULL,
[Math] [bit] NULL,
[Physics] [bit] NULL,
[Chemistry] [bit] NULL,
CONSTRAINT [PK_Results] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Results (StudentId, Math, Physics, Chemistry)
values
(201101,1,1,1),
(201102,0,1,1),
(201103,1,1,0),
(201104,0,1,0),
(201105,0,0,1)
How do i find which subject has been most attended/passed - 0 means not attended 1 means attended and passed.
May 19, 2011 at 6:13 am
What sort of result are you after?
SELECT attendedMath, attendedPhysics, attendedChemistry
FROM (SELECT COUNT(math) AS attendedMath
FROM results
WHERE math <> 0) mat
CROSS JOIN (SELECT COUNT(physics) AS attendedPhysics
FROM results
WHERE physics <> 0) phys
CROSS JOIN (SELECT COUNT(chemistry) AS attendedChemistry
FROM results
WHERE chemistry <> 0) chem
/*
attendedMath attendedPhysics attendedChemistry
------------ --------------- -----------------
2 4 3
*/
The above gives you the total number of times each subject was attended.
May 19, 2011 at 6:31 am
I used the following simple query and reached on same results as your query results:
select sum(convert(tinyint,Math)) as Math, sum(convert(tinyint,Physics)) as Physics, sum(convert(tinyint,Chemistry)) as Chemistry from results
but i don't know which one subject has the max attended. I need result as
Top 1 SUBJECT
Math 2
Physics 4
Chemistry 3
so the TOP one would be Physics with 4 rows - so what would be the query to move column into rows to bring top 1st.
Shamshad Ali.
May 19, 2011 at 6:34 am
I Thing you are looking for
SELECT CASE
WHEN CASE
WHEN math_cnt < physics_cnt THEN physics_cnt
ELSE math_cnt
END < chemistry_cnt THEN chemistry_cnt
ELSE CASE
WHEN math_cnt < physics_cnt THEN physics_cnt
ELSE math_cnt
END
END [attended/passed],
CASE
WHEN CASE
WHEN math_cnt < physics_cnt THEN physics_cnt
ELSE math_cnt
END < chemistry_cnt THEN 'chemistry'
ELSE CASE
WHEN math_cnt < physics_cnt THEN 'physics'
ELSE 'math'
END
END AS [Subject]
FROM (SELECT Count(CASE
WHEN math = 1 THEN 1
ELSE NULL
END) math_cnt,
Count(CASE
WHEN physics = 1 THEN 1
ELSE NULL
END) physics_cnt,
Count(CASE
WHEN chemistry = 1 THEN 1
ELSE NULL
END) chemistry_cnt
FROM results)t
May 19, 2011 at 6:47 am
Can't we simple move the colum to rows and get TOP 1. I think there would be another way to do this - right?
I just want to make it sure that which one would be best way.... simple and easy to understand .... There are only 3 columns and much casing used, if there are 12 to 15 subjects then it would be more compled to write and understand.
Shamshad Ali.
May 19, 2011 at 7:00 am
;WITH highest_attendance AS (
SELECT subject, attendance,
ROW_NUMBER() OVER (ORDER BY attendance DESC) AS rn
FROM (SELECT 'math' AS subject, COUNT(math) AS attendance
FROM results
WHERE math <> 0
UNION ALL
SELECT 'physics' AS subject, COUNT(physics) AS attendance
FROM results
WHERE physics <> 0
UNION ALL
SELECT 'chemistry' AS subject, COUNT(chemistry) AS attendance
FROM results
WHERE chemistry <> 0) att )
SELECT subject, attendance FROM highest_attendance
WHERE rn = 1
May 19, 2011 at 7:15 am
If you don't know how many subject u have then use dynamic SQL and get the result.
May 19, 2011 at 7:25 am
OK, thanks for your suggest. could you plz. make a dynamic query example and let me know how it would be looks like?
The reason is there are different classes with different subjects - I wanted to make a general query or may be i need some Lookup for each class. I also wanted to print final report of all subjects in one as summary of exam/assessment as follows:
2 students passed/attended Maths
4 students passed/attended Physics
3 students passed/attended Chemistry
I wanted to make this myself so I asked you to have some rows kind of result to solve the final report to school principle.
Please don't mind.
Shamshad Ali.
May 19, 2011 at 7:34 am
This solved my query: Thank you very much for your time and efforts. appreciated.
;WITH highest_attendance
AS
(SELECT subject, attendance, ROW_NUMBER() OVER
(ORDER BY attendance DESC)
AS rn
FROM (
SELECT 'math' AS subject, COUNT(math) AS attendance FROM results WHERE math <> 0
UNION ALL
SELECT 'physics' AS subject, COUNT(physics) AS attendance FROM results WHERE physics <> 0
UNION ALL
SELECT 'chemistry' AS subject, COUNT(chemistry) AS attendance FROM results WHERE chemistry <> 0)
att )
SELECT convert(varchar(3), attendance) + ' students appeared and passed in ' + subject FROM highest_attendance
Shamshad Ali.
May 30, 2011 at 8:13 am
one scan table [Result] only
select total_res, [subject]
from
(
select total_res = SUM(CAST(res AS Int)), [subject]
from
(
select *
from Results src
unpivot (res FOR [subject] in ([Math], [Physics], [Chemistry]) )unp
)r
group by [subject]
)r
order by total_res
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply