double count

Hello
got a small group by problem, i cant figure out how to divide the count with
"total" count for "each" day :)
CREATE TABLE #Test (
A char(1) NOT NULL,
B char(1) NOT NULL,
Somedate datetime NOT NULL
)
INSERT INTO #Test(A,B)VALUES('A','A','2001-01-01')
INSERT INTO #Test(A,B)VALUES('A','A','2001-01-01')
INSERT INTO #Test(A,B)VALUES('A','B','2001-01-01')
INSERT INTO #Test(A,B)VALUES('A','C','2001-01-01')
INSERT INTO #Test(A,B)VALUES('A','C','2001-01-01')
INSERT INTO #Test(A,B)VALUES('B','A','2001-01-01')
INSERT INTO #Test(A,B)VALUES('B','A','2001-01-01')
INSERT INTO #Test(A,B)VALUES('B','B','2001-01-01')
INSERT INTO #Test(A,B)VALUES('B','A','2001-01-02')
INSERT INTO #Test(A,B)VALUES('B','A','2001-01-02')
INSERT INTO #Test(A,B)VALUES('B','B','2001-01-02')
SELECT * FROM #Test
/* A COUNT COUNT/TOTAL Somedate
A 2 0.40 2001-01-01
A 1 0.20 2001-01-01
A 2 0.40 2001-01-01
B 1 0.50 2001-01-01
B 1 0.50 2001-01-01
B 1 0.50 2001-01-02
B 1 0.50 2001-01-02
*/
DROP TABLE #TestSELECT
T.A,
T.B,
T.SomeDate,
COUNT(*),
(COUNT(*) * 1.0) / T1.theCount
FROM #Test T
JOIN
(
SELECT
A,
COUNT(*) AS TheCount
FROM #Test
GROUP BY A
) T1 ON T1.A = T.A
GROUP BY
T.A,
T.B,
T.SomeDate,
T1.theCount
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OZIvJGnzFHA.908@.tk2msftngp13.phx.gbl...
> Hello
> got a small group by problem, i cant figure out how to divide the count
> with
> "total" count for "each" day :)
>
> CREATE TABLE #Test (
> A char(1) NOT NULL,
> B char(1) NOT NULL,
> Somedate datetime NOT NULL
> )
>
> INSERT INTO #Test(A,B)VALUES('A','A','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('A','A','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('A','B','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('A','C','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('A','C','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('B','A','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('B','A','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('B','B','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('B','A','2001-01-02')
> INSERT INTO #Test(A,B)VALUES('B','A','2001-01-02')
> INSERT INTO #Test(A,B)VALUES('B','B','2001-01-02')
> SELECT * FROM #Test
> /* A COUNT COUNT/TOTAL Somedate
> A 2 0.40 2001-01-01
> A 1 0.20 2001-01-01
> A 2 0.40 2001-01-01
> B 1 0.50 2001-01-01
> B 1 0.50 2001-01-01
> B 1 0.50 2001-01-02
> B 1 0.50 2001-01-02
> */
> DROP TABLE #Test
>|||Fix your table to have a key so that there are no duplicates. That will save
you from dealing with complex formulations for simple queries in the first
place.
Anith