Writing View with 'Group By' clause

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

Other SQL Server Topics (2005)

Writing View with 'Group By' clause

Author

Topic

swapnamandava
Starting Member

2 Posts

Posted - 2008-04-21 : 01:02:53

Hi All,

I've the following view in my DB and I need to modify it in such a way that it should be grouped by 'categoryId' and display the count of rows that come under each categoryId. In brief, I need to use 'Group By' clause and 'Count/SUM' aggregate function.

Here is my view.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MessageCategoryView]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MessageCategoryView]
GO

CREATE VIEW MessageCategoryView AS
SELECT
m.subject SUBJECT,
m.senderid SENDERID,
m.createdate CREATEDATE,
m.completedate COMPLETEDATE,
m.format MSGFORMAT,
m.isconfidential ISCONFIDENTIAL,
m.queueid QUEUEID,
mc.messageid MESSAGEID,
mc.categoryid CATEGORYID,
cv.label CATEGORYLABEL,
cv.hierarchystring HIERARCHYSTRING,
cv.hotkeys HOTKEYS

FROM
kc_message m
JOIN kc_messagecategory mc on m.messageid = mc.messageid
JOIN CategoryView cv on mc.categoryid = cv.categoryid
WHERE
mc.type not in (3,5)

Can anyone please help me out in resolving this.

Thanks in advance,
Sujatha

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-21 : 01:52:36

when you do a group by, you need to group by all columns that are not referenced in an aggregate function. any columns not in the group by clause must appear as args in aggregate functions.

so you have to decide what aggregate function you want to apply to each of the other columns you aren't grouping by. MIN, MAX, AVG, STDEV, or what?

for instance:


CREATE VIEW MessageCategoryView AS
SELECT
min(m.subject) SUBJECT,
min(m.senderid) SENDERID,
min(m.createdate) CREATEDATE,
min(m.completedate) COMPLETEDATE,
min(m.format) MSGFORMAT,
min(m.isconfidential) ISCONFIDENTIAL,
min(m.queueid) QUEUEID,
min(mc.messageid) MESSAGEID,
mc.categoryid CATEGORYID,
min(cv.label) CATEGORYLABEL,
min(cv.hierarchystring) HIERARCHYSTRING,
min(cv.hotkeys) HOTKEYS,
count(*) as Kount

FROM
kc_message m
JOIN kc_messagecategory mc on m.messageid = mc.messageid
JOIN CategoryView cv on mc.categoryid = cv.categoryid
WHERE
mc.type not in (3,5)
group by mc.categoryid

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources