Can this be done?

• jon_woodrow

  Grasshopper

  Points: 15

  More actions

  August 22, 2008 at 4:18 am

  #196623

  Hi

  Fairly new to SQL. Creating a VB.NET page and could achieve the following using code behind but wanted to see if it is possible using SQL.

  I have a table containing goalscorer information. Every entry in the table is linked to UserID and a FixtureID. There are only 3 types of Fixtures so i want to display a leading goalscorer page on my web page as follows

  UserID Total League Cup Friendly

  1 10 5 5 0

  2 3 1 1 1

  3 2 0 0 2

  4 1 1 0 0

  I've currently got

  SELECT UserIDD, SUM(Friendlies) AS Expr1

  FROM (SELECT thcGoalScorers.UserId AS UserIDD, COUNT(thcGoalScorers.UserId) AS Friendlies

  FROM thcGoalScorers INNER JOIN

  thcFixtures ON thcGoalScorers.FixtureID = thcFixtures.FixtureId

  WHERE thcFixtures.FixtureType = 'Friendly'

  GROUP BY thcGoalScorers.UserId, thcFixtures.FixtureType) DERIVEDTBL

  GROUP BY UserIDD

  is it possible to add a 2nd SUM to the top select and use a similar method to return the results?

  is this the correct way to approach this?

  Many thanks

• Skandalis

  Hall of Fame

  Points: 3517

  More actions

  August 22, 2008 at 4:23 am

  #861761

  Hi Jon,

  It's possible. Jeff Moden wrote a very nice article of this not too long ago.

  Try this link:

  http://www.sqlservercentral.com/articles/T-SQL/63681/

• jon_woodrow

  Grasshopper

  Points: 15

  More actions

  August 22, 2008 at 5:12 am

  #861781

  Many thanks Liam, I'm sure the article will cover what I need

  Thanks again

  Jon