SQL-syntax help! - 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)

SQL-syntax help!

Author

Topic

thotef
Starting Member

3 Posts

Posted - 2009-03-04 : 08:33:15

Hi there!

Need help with this problem:

The following works fine...

SELECT COUNT(HitsEvent.Link_ID) AS nrOfClicks, HitsEvent.Link_ID, Links.Link_Name
FROM HitsEvent INNER JOIN Links
ON HitsEvent.Link_ID = Links.Link_ID
WHERE (HitsEvent.Link_ID <> - 1)
GROUP BY HitsEvent.Link_ID, LinWhere the result looks like this:

nrOfClicks Link_ID Link_Name
40231 8196 Apollo
38555 8198 Betty
37886 8171 AFG
13078 6766 Betty

As You can see the name Betty is occuring twice and that is correct because that Link_Name exists twice in database because two persons have made a link that points to the place...

What I want now is to extend the query so that it SUM:s and aggregate Link_Names so that Betty just occurs once but have nrOfClicks totalized for all the ocurrencys.

I know that this is a peace of cace by doing a stored procedur and using a temporary table but by many reasons I want to do this with just one sql-statement from the webb...

Thanks in advance // TT

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-04 : 08:45:16

[code]SELECT COUNT(HitsEvent.Link_ID) AS nrOfClicks, Links.Link_Name
FROM HitsEvent INNER JOIN Links
ON HitsEvent.Link_ID = Links.Link_ID
WHERE HitsEvent.Link_ID <> - 1
GROUP BY Links.Link_Name[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 08:57:46

quote:
Originally posted by thotef

Hi there!

Need help with this problem:

The following works fine...

SELECT COUNT(HitsEvent.Link_ID) AS nrOfClicks, HitsEvent.Link_ID, Links.Link_Name
FROM HitsEvent INNER JOIN Links
ON HitsEvent.Link_ID = Links.Link_ID
WHERE (HitsEvent.Link_ID <> - 1)
GROUP BY HitsEvent.Link_ID, LinWhere the result looks like this:

nrOfClicks Link_ID Link_Name
40231 8196 Apollo
38555 8198 Betty
37886 8171 AFG
13078 6766 Betty

As You can see the name Betty is occuring twice and that is correct because that Link_Name exists twice in database because two persons have made a link that points to the place...

What I want now is to extend the query so that it SUM:s and aggregate Link_Names so that Betty just occurs once but have nrOfClicks totalized for all the ocurrencys.

I know that this is a peace of cace by doing a stored procedur and using a temporary table but by many reasons I want to do this with just one sql-statement from the webb...

Thanks in advance // TT

when you merge the two Betty records, wht should be link_id value returned?

thotef
Starting Member

3 Posts

Posted - 2009-03-04 : 09:16:19

quote:

Originally posted by sakets_2000

SELECT COUNT(HitsEvent.Link_ID) AS nrOfClicks, Links.Link_Name
FROM HitsEvent INNER JOIN Links
ON HitsEvent.Link_ID = Links.Link_ID
WHERE HitsEvent.Link_ID <> - 1
GROUP BY Links.Link_Name

Thanx a lot worked fine!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources