a simple query .. but complex - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

a simple query .. but complex

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2010-01-12 : 19:37:40

Hi Guys, i have a question . need help .

student( student_key varchar(20),
subject varchar(5),
marks int,
subject_count int,
update_date datetime
)

student_Key, subject, marks,Subject_count, upd_date
(
'100','PHY',50,0, getdate()
'200','PHY',60,0, getdate()
'300','PHY',65,0, getdate()
'400','PHY',50,0, getdate()
'500','PHY',40,0, getdate()
'100','CHEM',50,0, getdate()
'200','CHEM',60,0, getdate()
'300','MATH',65,0, getdate()
'500','PHY',40,0, getdate()

)

Want to add these 3 summary records to the same table.
basically it will count no of occurences of 'PHY' for that day and
inserts an entry as 'Physics'

'Physics','',0,5, getdate() -- here 5 is the count for physics
'Chemistry','',0,2, getdate() --here 2 is the count for chemistry
'Mathematics','',0,1, getdate() -- here 1 is the count for maths.

Chandragupta Mourya

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-12 : 20:19:52

I don't think summary information should be stored in this table, but here you go anyway:

INSERT INTO Student(student_Key, subject, marks,Subject_count, upd_date)
SELECT
CASE
WHEN s.subject = 'PHY' THEN 'Physics'
WHEN s.subject = 'CHEM' THEN 'Chemistry'
WHEN s.subject = 'MATH' THEN 'Mathematics'
END,
'',
0,
t.SubjectCount,
GETDATE()
FROM Student s
JOIN (SELECT subject, COUNT(*) AS SubjectCount FROM Student GROUP BY subject) t
ON s.subject = t.subject

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-01-12 : 20:27:51

First a few questions:
Why would you want to add summary data to the same table?
What are you using as the primary key and, therefore, what values are needed in the table for uniqueness?

You can get the counts by aggregating over the subject:

select Subject, count(*) SubjCount
from MyTable
where upd_date = GetDate() -- Strip off the time first (left as an exercise to the student)
group by Subject

HTH

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 01:45:23

I guess its for some reporting purpose in which case you can very easily do this aggregation at front end. most reporting tools provide expressions to do this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources