need help in sql.....

Question

0.00/5 (No votes)

See more:

sir,i have 2 tables that contain column like :-

1)First table is "ClassRoom"

"ClassRoom" "ClassStrength"
"ME-3" 36
"C-202" 49

2)Second Table is Matrix

Roomno SeatNo
"ME-3" 1
"ME-3" 2
"ME-3" 3
"C-202" 1
"C-202" 2

i have a sql query:-

SQL

select distinct list,max(cc) from
(
( select distinct Convert(varchar,ClassRoom,106) +' > '+ Convert(varchar,ClassStrength,106) +' > '+  '0'   as List ,Classroom,0 as cc from ClassRoom
left  join Matrix on matrix.roomno=classroom.classroom
where classstrength is not null
group by classroom.classroom,ClassStrength   )
union all
( select distinct Convert(varchar,ClassRoom,106) +' > '+ Convert(varchar,ClassStrength,106) +' > '+ case when Count(seatno) > 0  then Convert(varchar,Count(Seatno),106) else '0'  end as List,Classroom ,Count(Seatno) as cc from ClassRoom
left join Matrix on matrix.roomno=classroom.classroom
where examid=1 and examname='MST1' and classstrength is not null
group by classroom.classroom,ClassStrength
)
) as t
group by list,Classroom

Output Like:-

"C-202 > 49 > 0" "0"
"ME-3 > 36 > 0" "0"
"ME-3 > 36 > 36" "36"
"A-302 > 36 > 0" "0"
"A-302 > 36 > 36" "36"
"A-303 > 42 > 0" "0"

But i need Output like:-

"C-202 > 49 > 0" "0"
"ME-3 > 36 > 36" "36"
"A-302 > 36 > 36" "36"
"A-303 > 42 > 0" "0"

how can i do it..

Posted 21-Dec-14 19:54pm

TCS54321

Updated 21-Dec-14 21:09pm

v2

Add a Solution

Comments

Maciej Los 22-Dec-14 2:35am

What's input data? Use "Improve question" widget!

TCS54321 22-Dec-14 3:09am

i have update my ques. how u get any solutions

Jörgen Andersson 22-Dec-14 3:16am

Also update your question with what database and version there of you are using

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Answer 1 · 2014-12-21T21:21:00

You should remove UNION and use only lower one. Also, what is the point of CONVERT since the ClassRoom isn't datetime field?

Count will already return 0 so you don't need CASE WHEN returning 0, just give the value, you're using LEFT JOIN which will return all the values so again, UNION IS NOT NEEDED.

SQL

SELECT DISTINCT list, MAX(cc) FROM (
    SELECT DISTINCT CAST(ClassRoom as varchar(10)) +' > ' + CAST(ClassStrength as varchar(5)) +' > ' + CAST(Count(Seatno) as varchar(10)) as List, Classroom ,Count(Seatno) as cc
FROM
    ClassRoom
    LEFT JOIN Matrix on matrix.roomno=classroom.classroom
WHERE
    examid=1 and examname='MST1' and classstrength is not null
GROUP BY
    classroom.classroom,ClassStrength
) t
GROUP BY list

If I missed something important with CONVERT (maybe its your culture settings dependant) simply change casts back to converts :)

If this helps please take time to accept the solution. Thank you.