cancel
Showing results for 
Search instead for 
Did you mean: 

Query grouping question

Former Member
0 Kudos

I am trying to get the following query to group on the acctcode field. This is to show budget detail with a column for each month grouped by account. The month indicator (line_id) is the problem,

SELECT t0.acctcode,

(case when (t0.line_id) = 0 then sum(t0.debltotal) end) as January,

(case when (t0.line_id) = 1 then sum(t0.debltotal) end) as February,

(case when (t0.line_id) = 2 then sum(t0.debltotal) end) as March

from bgt1 t0 inner join OBGT t1 on T0.budgid = t1.absid

where t1.instance = '[%0]'

group by t0.line_id, t0.acctcode

How can I eliminate t0.line_id in the group by clause? Leaving it out of course creates an error.

Thanks

View Entire Topic
Former Member
0 Kudos

Try this one:

Declare @instance int

Set @instance = [%0\]

SELECT T2.AccountCode, SUM(T2.January) AS 'Jan', SUM(T2.February) AS 'Feb', SUM(T2.March) AS 'Mar'

FROM

(SELECT t0.acctcode as 'AccontCode',

(case when (t0.line_id) = 0 then sum(t0.debltotal) end) as 'January',

(case when (t0.line_id) = 1 then sum(t0.debltotal) end) as 'February',

(case when (t0.line_id) = 2 then sum(t0.debltotal) end) as 'March'

from bgt1 t0 inner join OBGT t1 on T0.budgid = t1.absid

where t1.instance = @instance

group by t0.line_id, t0.acctcode) T2

GROUP BY T2.AccountCode

Thanks,

Gordon