SQL count - 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

General SQL Server Forums

New to SQL Server Programming

SQL count

Author

Topic

gparadis3
Starting Member

11 Posts

Posted - 2005-11-29 : 00:57:41

Hi All,
i have a problem here..

let say i have 2 tables (student - studentID, studentName and details - studentID, Week, Violence).

my query for a view is as below:

SELECT
s.studentName AS Student, COUNT(d.studentID) AS TotalViolence, d.Week
FROM
dbo.Student s LEFT OUTER JOIN dbo.Detail d
ON
s.studentID = d.studentID
WHERE d.week in
(SELECT DISTINCT TOP 24 week AS WW FROM Detail ORDER BY ww DESC)
GROUP BY s.StudentName, d.Week

i want to map this view in a graph. the query will only return studentID which in table detail. I want to have all studentID and if that student dont have violence record for that week, it will return zero in TotalViolence.How can i do that?

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-11-29 : 01:09:50

try this..
SELECT
s.studentName AS Student, COUNT(d.studentID) AS TotalViolence, d.Week
FROM
dbo.Students LEFT OUTER JOIN (select * from dbo.Detail WHERE week in
(SELECT DISTINCT TOP 24 week AS WW FROM Detail ))
d
ON
s.studentID = d.studentID
GROUP BY s.StudentName, d.Week

gparadis3
Starting Member

11 Posts

Posted - 2005-11-29 : 01:40:33

thanks shallu.. but it still not like what i want.. it will capture 0 to the studentID but not by week. The graph look so weird.

i give an example here:
in week 5, a student with studentID=4000 made 1 violence
in week 6, that student didnt do any violence.
in week 7, he did 2 violences. the graph will maintain 1 for week 6. i need to capture in week 6, that student didn't do any violence.

Info about graph:
type: line graph
x: week
y: total violence

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-29 : 01:58:24

See if this helps
http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx

Madhivanan

Failing to plan is Planning to fail

gparadis3
Starting Member

11 Posts

Posted - 2005-11-29 : 05:13:48

Thanks for the site!!! Got the result as i wish but dunno whether the query is rite or wrong.. :) hopefully it is rite..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-29 : 08:44:49

quote:
Originally posted by gparadis3

Thanks for the site!!! Got the result as i wish but dunno whether the query is rite or wrong.. :) hopefully it is rite..

Did you build query using the logic used at that weblog?

Madhivanan

Failing to plan is Planning to fail

gparadis3
Starting Member

11 Posts

Posted - 2005-11-29 : 10:37:44

i did base on d logic that i understand.. that's y i dunno whether it is rite or wrong..