subselect med count(*)

denna funkar finfint

	Select a.Resolution, month(b.CreateDate) As [Month]
	From Stat a, Stat b
	Where b.CreateDate Between dbo.ConvertDate('2005-02-25', 'year', 1) 
	And dbo.ConvertDate('2005-02-25', 'year', 2)
	Group By  a.Resolution, month(b.CreateDate) 
	Order By month(b.CreateDate),
	Convert(int, left(a.Resolution, charindex('x', a.Resolution,1)-1)), 
	Convert(int, right(a.Resolution, len(a.Resolution) - charindex('x', a.Resolution,1)))

se bild på resultatet

sen vill jag lägga till en subselect till den:

	Select a.Resolution, month(b.CreateDate) As [Month],
		(Select count(*) As Total 
		 From Stat c
		 Where c.Resolution = a.Resolution And month(c.CreateDate) = month(b.CreateDate)
		 )
	From Stat a, Stat b
	Where b.CreateDate Between dbo.ConvertDate('2005-02-25', 'year', 1) 
	And dbo.ConvertDate('2005-02-25', 'year', 2)
	Group By  a.Resolution, month(b.CreateDate) 
	Order By month(b.CreateDate),
	Convert(int, left(a.Resolution, charindex('x', a.Resolution,1)-1)), 
	Convert(int, right(a.Resolution, len(a.Resolution) - charindex('x', a.Resolution,1)))

då får jag: internal server error i query analyzern
tar jag bort month(b.CreateDate) i group by så funkar det men resultatet blir då helt fel!

jag vill lista antalet som har tex. 800x600 under januari
hur ska selecten se ut?

Varför en subselect?

Select a.Resolution, month(b.CreateDate) As [Month],
	count(*) As Total 
	From Stat a, Stat b
	Where b.CreateDate Between dbo.ConvertDate('2005-02-25', 'year', 1) 
	And dbo.ConvertDate('2005-02-25', 'year', 2)
	Group By  a.Resolution, month(b.CreateDate) 
	Order By month(b.CreateDate),
	Convert(int, left(a.Resolution, charindex('x', a.Resolution,1)-1)), 
	Convert(int, right(a.Resolution, len(a.Resolution) - charindex('x', a.Resolution,1)))

Jag har svårt att inse vad du vill uppnå, t.ex. så finns det inget villkor p a.

se bild för resultat.

resultatet blev helt galet! det ska bli ex. 2 st 800x600 i februari och ingen i januari.

förklaring:
tabell a grupperar ihop alla sorters upplösningar som registrerats totalt i loggen under detta år, dvs även fast ingen användare har 800x600 under januari så ska den visas med värdet noll.

tabell b hämtar alla månader som registrerats totalt i loggen under detta år, dvs januari och februari (än så länge)

tabell c ska sedan hämta antalet besökare som har t.ex 800x600 under januari.

jag antog bara att en subselect var nödvändig men kanske går det att få till den kod du börjat på?

Select a.Resolution, b.[Month],count(b.resolution)
	From (select distinct resolution from stat) a
        left join 
        (select month(CreateDate) as [month], resolution from stat
          where CreateDate Between dbo.ConvertDate('2005-02-25', 'year', 1) 
            and dbo.ConvertDate('2005-02-25', 'year', 2) ) b 
        on a.resolution = b.resolution
	Group By a.Resolution, b.[month] 
	Order By b.[month],
	Convert(int, left(a.Resolution, charindex('x', a.Resolution,1)-1)), 
	Convert(int, right(a.Resolution, len(a.Resolution) - charindex('x', a.Resolution,1)))

fantastiskt! :bire

nej föresten, det blev inte heller riktigt rätt! :)
nu visar den bara de upplösningar där det finns ett värde på men jag skulle vilja visa alla upplösningar under både jan. och feb med resultat noll om det inte finns nått värde.
resultatet ska alltså bli ex.
jan------------------
800x600 0 st
1024x768 1 st
1280x1024 0 st

feb------------------
800x600 3 st
1024x768 8 st
1280x1024 5 st

nu blir det enlig bifogad bild.

en nödlösning som funkar, men man borde ju kunna få ihop det utan en tmp tabell förståss!

	DECLARE @i AS tinyint
	DECLARE @CreateDate AS datetime
	SET @CreateDate = '2005-02-28'

	CREATE TABLE #tmp (IDno int)
	
	SET @i = 1
	WHILE @i <= month(@CreateDate)
		BEGIN
		INSERT INTO #tmp (IDno) VALUES (@i)
		SET @i = @i+1
		END

	SELECT a.Resolution, t.IDno,(
		SELECT count(resolution) 
		FROM Stat 
		WHERE month(CreateDate) = t.IDno And Resolution = a.Resolution)
	FROM Stat a, #tmp t
	GROUP BY a.Resolution, t.IDno 
	ORDER BY t.IDno,
	CONVERT(int, left(a.Resolution, CHARINDEX('x', a.Resolution,1)-1)), 
	CONVERT(int, right(a.Resolution, LEN(a.Resolution) - CHARINDEX('x', a.Resolution,1)))

	DROP TABLE #tmp

Select a.Resolution, b.[Month],count(c.createDate)
	From (select distinct resolution from stat) a
        cross join 
        (select distinct month(CreateDate) from stat
          where CreateDate Between dbo.ConvertDate('2005-02-25', 'year', 1) 
            and dbo.ConvertDate('2005-02-25', 'year', 2) ) b 
        left join stat c
        on a.resolution = c.resolution
        and b.[month] = month(c.createDate)
        and c.CreateDate Between dbo.ConvertDate('2005-02-25', 'year', 1) 
            and dbo.ConvertDate('2005-02-25', 'year', 2)
	Group By a.Resolution, b.[month] 
	Order By b.[month],
	Convert(int, left(a.Resolution, charindex('x', a.Resolution,1)-1)), 
	Convert(int, right(a.Resolution, len(a.Resolution) - charindex('x', a.Resolution,1)))

ja vad säger man!!! :bire