Inline Query

• Martin Carolan

  Grasshopper

  Points: 19

  More actions

  August 12, 2004 at 5:24 am

  #162431

  This may be a stupid question but here it goes...

  I am writing a stored procedure and trying to do an inline query on the same table:

  Code: ALTER PROCEDURE djLink_sp_GetBrowserStats

  AS

  Declare

  @TotalBrowsers int

  Select

  @TotalBrowsers = Count(*) From djLink_tblBrowsers

  Select

  Distinct

  BrowserName,

  (

  Select Count(*) From djLink_tblBrowsers Where BrowserName = djLink_tblBrowsers.BrowserName) As Total,

  ((

  Select Count(*) From djLink_tblBrowsers Where BrowserName = BrowserName) / @TotalBrowsers * 100) As Percentage

  From

  djLink_tblBrowsers

  The problem is on this line (and the line preceeding):

  (Select Count(*) From djLink_tblBrowsers Where BrowserName = djLink_tblBrowsers.BrowserName) As Total

  Basically because I am doing the query in the same table it is getting confused and counting all rows, not just ones with the same browser name.

  Is there anyway to get around this?

  Thanks, Mart

• Jonathan Stokes

  SSCrazy Eights

  Points: 9861

  More actions

  August 12, 2004 at 7:01 am

  #518650

  You need to name the table and column that you want to count so that sql recognises it. Also, it is quicker to count the primary key than to count all, so should add some speed, too.

  ---

  ------------------------------
  The Users are always right - when I'm not wrong!

• Dølle Døck

  Mr or Mrs. 500

  Points: 577

  More actions

  August 12, 2004 at 8:17 am

  #518681

  Try this:

  DECLARE @TotalBrowsers Int

  SET @TotalBrowsers = (Select Count(*) From djLink_tblBrowsers)

  Select

   BrowserName,

   COUNT(BrowserName) As Total,

   COUNT(BrowserName)* 100/ @TotalBrowsers As Percentage

  From djLink_tblBrowsers djB

  GROUP BY BrowserName

  ---

  Regards,

  Anders Dæmroen
  epsilon.no

• Martin Carolan

  Grasshopper

  Points: 19

  More actions

  August 12, 2004 at 10:05 am

  #518721

  Thanks loads, worked like a charm