counting fields in a group - 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

Other Forums

MS Access

counting fields in a group

Author

Topic

tamoren
Starting Member

4 Posts

Posted - 2006-10-10 : 06:34:06

I'm using the Total row in my query.
There's: GroupBy, Min, Max and Average.
I want a fifth column that counts the number of records calculated in each group (i.e. number of values on which the min/max/avg were calculated).
I tried Count, but it obviously doesn't work. What did it actually count?
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 06:45:55

Hard to tell without seeing your query.
Post it here.

Peter Larsson
Helsingborg, Sweden

tamoren
Starting Member

4 Posts

Posted - 2006-10-10 : 06:58:16

SELECT [InputCases].Constituent, [InputCases].Fraction, [InputCases].Units, Min([InputCases].ReportedValueAdj) AS MinOfReportedValueAdj, Max([InputCases].ReportedValueAdj) AS MaxOfReportedValueAdj, Avg([InputCases].ReportedValueAdj) AS AvgOfReportedValueAdj
FROM [InputCases]
GROUP BY [InputCases].Constituent, [InputCases].Fraction, [InputCases].Units;

The records are grouped by [InputCases].Constituent (but what's the meaning of the two other GroupBy's?)
I need to know how many instances of a constituent i've got in each group.
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 07:09:15

You can delete the two other GROUP BYs if you want to, but then you have to delete the same two columns in the SELECT too. Try that and see what that gives.

Also try

SELECT		[InputCases].Constituent,
		COUNT(*) CountOfConstituents
FROM		[InputCases]
GROUP BY	[InputCases].Constituent

Peter Larsson
Helsingborg, Sweden

tamoren
Starting Member

4 Posts

Posted - 2006-10-10 : 07:20:54

Thanks, but it says "Syntax error (missing operator)..."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 07:34:08

Put in a AS between COUNT(*) and CountOfConstituents

Peter Larsson
Helsingborg, Sweden

tamoren
Starting Member

4 Posts

Posted - 2006-10-10 : 07:44:16

Great, Thanks!

benjo
Starting Member

8 Posts

Posted - 2006-10-26 : 04:02:03

peso, you are intelligent. good luck

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-26 : 04:45:48

Thanks?

Peter Larsson
Helsingborg, Sweden

om
Starting Member

8 Posts

Posted - 2006-10-27 : 13:59:57

quote:
Originally posted by Peso

Put in a AS between COUNT(*) and CountOfConstituents
put count(*) to count row without null

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources