SQL Server Performance

Sum and Count in one query - SQL Server 2005

Discussion in 'ALL SQL SERVER QUESTIONS' started by Jon M, Feb 9, 2012.

  1. Jon M Member

    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.
  2. davidfarr Member

    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'
  3. Jon M Member

    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.

Share This Page