Counting up grouped results - 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 2000 Forums

Transact-SQL (2000)

Counting up grouped results

Author

Topic

tomonage
Starting Member

1 Post

Posted - 2004-07-21 : 07:57:33

Cheers,

I have the following table:

Field1 Field2 Field3
A a 2
A b 5
A c 7
B a 3
B c 5
C d 5
C f 9

And I want to get the same table (Ordert by Field 1, 2 and 3, like above) but with a "counter" in the new field 4. This counter should count the grouped elements:

Field1 Field2 Field3 Field4
A a 2 1
A b 5 2
A c 7 3
B a 3 1
B c 5 2
C d 5 1
C f 9 2

I think that somethink like

SELECT Field1, Field2, Field3 , X from table group by Field1, field2

will do it, but I dont know how to implement the counter X.

Can anybody help me?

Thanks

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-21 : 08:28:15

[code]
SELECT Field1,Field2,Field3,COUNT(1) AS Field4
FROM MyTable
GROUP BY Field1,Field2,Field3
ORDER BY Field1,Field2,Field3
[/code]

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-21 : 09:48:07

Something like this would work. I'm sure you'll have to play with it.


DECLARE @totals TABLE(
	ident INT IDENTITY(1,1) PRIMARY KEY,
	col1 CHAR(1),
	col2 CHAR(1),
	col3 INT)

INSERT @totals(col1, col2, col3)

	SELECT 'A', 'a', 2
	UNION ALL
	SELECT 'A', 'b', 5
	UNION ALL
	SELECT 'A', 'c', 7
	UNION ALL
	SELECT 'B', 'a', 3
	UNION ALL
	SELECT 'B', 'c', 5
	UNION ALL
	SELECT 'C', 'd', 5
	UNION ALL
	SELECT 'C', 'f', 9

SELECT
	t.ident,
	t.col1,
	t.col2,
	t.col3,
	(SELECT COUNT(col1) FROM @totals WHERE ident <= t.ident AND col1 = t.col1) AS RtotalField
FROM
	@totals t
GROUP BY
	t.col1,
	t.col2,
	t.col3,
	t.ident

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources