Group By Total - 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

SQL Server Development (2000)

Group By Total

Author

Topic

Knarf180
Starting Member

42 Posts

Posted - 2004-11-03 : 10:42:09

Another morning another problem...

Is it possible to have a grand total which bypasses the Group By statement?

Select Field1, Count(*) from Table Group By Field1

That is an example of what I can currently using. It gives me the number of accurences for each Field1.

As for the grand total, I would like to return Sum(Field1). But need this to ignore the Group By statement so it would calculate correctly.

Any ideas?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-03 : 10:51:41

usually this should be done in the presentation layer (i.e., the report you are producing with this SQL).

in you need/want to do it in T-SQL can use a UNION :

Select Field1, Count(*) from Table Group By Field1
union all
select 'Total', count(*) from Table

YOu also can use WITH ROLLUP, I believe:

Select Field1, Count(*) from Table Group By Field1 With Rollup

- Jeff

Knarf180
Starting Member

42 Posts

Posted - 2004-11-03 : 11:15:09

Yeah, I would love to do it at the presentation layer but my report software (Active Reports) is unable to calculate the totals until you are either at the end of a group or page.. My data is needed in the body of the report to calculate percentages.

I'm trying not to use a Union since it would require a second query increasing report generation time which is already around 8 minutes.

I'm took a look at the With Rollup method but not sure if this is going to do. To complicate things a little more I'm storing the data in variable and tossing them into a temp table. So my query looks more like..

Select @Fee = Field1, @Frequency = Count(*) Group By Field1

I guess I'm going to be stuck using a Union.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-03 : 11:24:50

that's a different story, calcing percentages. most reports will let you do this easily, but I don't know about active reports so i can't say for sure.

but you can do this in sql pretty easily, and in this case there is no UNION at all but something like this:

select Field, 1.0 * COUNT(*) / (select count(*) from YourTable) as Pct
from Yourtable
group by field

or

select A.field, A.FieldCount * 1.0 / B.Total
from
(select field, count(*) as FieldCount from YourTable group by field) A
cross join
(select count(*) as Total from yourtable) B

- Jeff

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-11-03 : 12:48:15

If your talking about a relatively small number of records returned, Say under 1000.
I have dropped the entire SP into a temp table with additional blank fields for the total and avg.
Sum and avg. the temp table into variables then drop them into the blank fields and return the whole temp table.

Its an option anyway.

edit: sorry Avg was what
I was doing. Gads I need more coffee!

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources