Need help in view - 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

Need help in view

Author

Topic

tejaswini.c
Starting Member

4 Posts

Posted - 2010-06-25 : 02:34:37

SELECT
dbo.links.link_category, dbo.links.link_id, dbo.links.link_author, dbo.users.user_login AS link_authorName, dbo.links.link_status, dbo.links.link_comments,dbo.links.link_url, dbo.links.link_tags,
dbo.categories.category_name,GETDATE() AS serverTimeNow, dbo.links.link_hits, dbo.links.link_editorPicked

FROM dbo.links INNER JOIN
dbo.categories ON dbo.links.link_category = dbo.categories.category_id INNER JOIN
dbo.users ON dbo.links.link_author = dbo.users.user_id INNER JOIN
dbo.votes ON dbo.links.link_id = dbo.votes.vote_link_id LEFT OUTER JOIN
dbo.linkimage ON dbo.links.link_id = dbo.linkimage.linkid

This is my view .

I want to add the folllowing statement to it..

Select vote_link_id,sum(vote_value) from votes
group by vote_link_id

Can anybody help me..to integrate the statement?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-25 : 02:42:55

[code]
SELECT
dbo.links.link_category, dbo.links.link_id, dbo.links.link_author, dbo.users.user_login AS link_authorName, dbo.links.link_status, dbo.links.link_comments,dbo.links.link_url, dbo.links.link_tags,
dbo.categories.category_name,GETDATE() AS serverTimeNow, dbo.links.link_hits, dbo.links.link_editorPicked
, vote_value
FROM dbo.links INNER JOIN
dbo.categories ON dbo.links.link_category = dbo.categories.category_id INNER JOIN
dbo.users ON dbo.links.link_author = dbo.users.user_id INNER JOIN
dbo.votes ON dbo.links.link_id = dbo.votes.vote_link_id LEFT OUTER JOIN
dbo.linkimage ON dbo.links.link_id = dbo.linkimage.linkid

INNER JOIN
(
Select vote_link_id, vote_value = sum(vote_value)
from votes
group by vote_link_id
) v on dbo.votes.vote_link_id = v.vote_link_id

[/code]

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-25 : 02:48:28

Given that you now have duplicate rows in your result set; What you basically do is add the SUM() in the select, then you just copy/paste all the other columns to the group by. Without doing too much proofreading I think this is what you need:

SELECT 
dbo.links.link_category, dbo.links.link_id, dbo.links.link_author, 
dbo.users.user_login AS link_authorName, dbo.links.link_status, 
dbo.links.link_comments,dbo.links.link_url, dbo.links.link_tags, 
dbo.categories.category_name,GETDATE() AS serverTimeNow, dbo.links.link_hits, 
dbo.links.link_editorPicked, sum(vote_value)

FROM dbo.links INNER JOIN
dbo.categories ON dbo.links.link_category = dbo.categories.category_id INNER JOIN
dbo.users ON dbo.links.link_author = dbo.users.user_id INNER JOIN
dbo.votes ON dbo.links.link_id = dbo.votes.vote_link_id LEFT OUTER JOIN
dbo.linkimage ON dbo.links.link_id = dbo.linkimage.linkid
GROUP BY dbo.links.link_category, dbo.links.link_id, dbo.links.link_author, 
dbo.users.user_login AS link_authorName, dbo.links.link_status, 
dbo.links.link_comments,dbo.links.link_url, dbo.links.link_tags, 
dbo.categories.category_name,GETDATE() AS serverTimeNow, dbo.links.link_hits, 
dbo.links.link_editorPicked

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

tejaswini.c
Starting Member

4 Posts

Posted - 2010-06-25 : 02:56:56

Hi khtan,
I m getting duplicate rows and even I am not able to get the vote_value as sum of all the available values.
Can u help?

Devart
Posting Yak Master

102 Posts

Posted - 2010-06-25 : 03:09:45

Hello tejaswini.c,

You can try it:

SELECT
dbo.links.link_category,
dbo.links.link_id,
dbo.links.link_author,
dbo.users.user_login AS link_authorName,
dbo.links.link_status,
dbo.links.link_comments,
dbo.links.link_url,
dbo.links.link_tags,
dbo.categories.category_name,
GETDATE() AS serverTimeNow,
dbo.links.link_hits,
dbo.links.link_editorPicked,
(select sum(vote_value) from votes where vote_link_id=dbo.votes.vote_link_id) as sum_vote_value
FROM dbo.links
INNER JOIN dbo.categories
ON dbo.links.link_category = dbo.categories.category_id
INNER JOIN dbo.users
ON dbo.links.link_author = dbo.users.user_id
INNER JOIN dbo.votes
ON dbo.links.link_id = dbo.votes.vote_link_id
LEFT OUTER JOIN dbo.linkimage
ON dbo.links.link_id = dbo.linkimage.linkid

Best regards,

Devart,
Tools for SQL Server
http://www.devart.com/dbforge/sql

tejaswini.c
Starting Member

4 Posts

Posted - 2010-06-25 : 03:18:54

Thanks Devart,
it's giving the exact sum but it's not showing distinct rows.
in the output I m getting 2 records of same ids.

Can u help me plz

tejaswini.c
Starting Member

4 Posts

Posted - 2010-06-25 : 03:19:58

It's showing two rows for same linkid.
Can u help me?

quote:
Originally posted by Devart

Hello tejaswini.c,

You can try it:

SELECT
dbo.links.link_category,
dbo.links.link_id,
dbo.links.link_author,
dbo.users.user_login AS link_authorName,
dbo.links.link_status,
dbo.links.link_comments,
dbo.links.link_url,
dbo.links.link_tags,
dbo.categories.category_name,
GETDATE() AS serverTimeNow,
dbo.links.link_hits,
dbo.links.link_editorPicked,
(select sum(vote_value) from votes where vote_link_id=dbo.votes.vote_link_id) as sum_vote_value
FROM dbo.links
INNER JOIN dbo.categories
ON dbo.links.link_category = dbo.categories.category_id
INNER JOIN dbo.users
ON dbo.links.link_author = dbo.users.user_id
INNER JOIN dbo.votes
ON dbo.links.link_id = dbo.votes.vote_link_id
LEFT OUTER JOIN dbo.linkimage
ON dbo.links.link_id = dbo.linkimage.linkid

Best regards,

Devart,
Tools for SQL Server
http://www.devart.com/dbforge/sql

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-25 : 03:39:13

you mean your original query does not have duplicate vote_link_id in the result ?

KH
[spoiler]Time is always against us[/spoiler]

Devart
Posting Yak Master

102 Posts

Posted - 2010-06-25 : 03:48:10

Hello,

Try to use DISTINCT after SELECT.

SELECT DISTINCT
dbo.links.link_category,
dbo.links.link_id,
......

Best regards,

Devart,
Tools for SQL Server
http://www.devart.com/dbforge/sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources