Sum and Count in one query - SQL Server 2005

Hello All,

Is it possible to achieve the below desired output, using one query only?
date starttime endtime length field1 field2
2011-01-31 171852 171852 0 123456 1111111111
2011-02-07 120335 120405 1 789123 1111111111
1 2
2011-01-21 171852 171911 1 121212 2222222222
2011-01-31 171852 171852 0 343434 2222222222
2011-02-07 120435 120505 1 565656 2222222222
2 3

Lines with 1 2 and 2 3 are the sum of length and count of field1 consecutively. Also, they are grouped according to field2.

Note that sum and count must be under the field length and field1 - I am unable to post my desired output properly on this forum.

Thanks.

Keep in mind that every row of a returned query result will always contain the same number of columns/fields and that each column will always have a consistent data type on every row.
You therefore cannot generate a single result that has 6 date columns in some rows and 2 integer columns on other rows.
Also, the GROUP BY clause of a query applies to every row of a result, you cannot combine grouped aggregates on some rows with ungrouped data on other rows in the same result.

If the data types in your columns are text (varchar,char,nvarchar, etc.) then there is possibly a way of faking the output that you want using a UNION clause, something like this:

select convert(varchar,[date]) as [date],convert(varchar,starttime) as starttime,endtime,[length],field1,field2
from MyTable where field2='1111111111'
union all
select convert(varchar,sum([length])), convert(varchar,count(field1)),null,null,null,null
from MyTable where field2='1111111111'

Thanks for the input. I was actually trying to use an aggregate function "GROUPING" WITH ROLLUP operator but the result is messed up. In the meantime, I used the "COMPUTE" clause.