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

Accepted Solutions (1)

Accepted Solutions (1)

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

Answers (1)

Answers (1)

former_member583013
Active Contributor
0 Kudos

Peter,

You could use this approach. Add a LEFT JOIN for the other 8 months from May thru December and have the alias T6, T7....T13. Use each of the alias and add SUM(T6.DeblTotal), SUM(T7.DeblTotal), ....SUM(T13.DeblTotal) to the SELECT statement.

SELECT T0.AcctCode, SUM(T2.DeblTotal) [January], SUM(T3.DeblTotal) [February],
SUM(T4.DeblTotal) [March], SUM(T5.DeblTotal) [April]
FROM [dbo].[BGT1] T0 INNER JOIN [DBO].[OBGT] T1 on T0.BudgId = T1.AbsId
LEFT OUTER  JOIN [dbo].[BGT1] T2  ON  T2.AcctCode = T0.AcctCode  AND  T0.Line_Id = 0
LEFT OUTER  JOIN [dbo].[BGT1] T3  ON  T3.AcctCode = T0.AcctCode  AND  T0.Line_Id = 1
LEFT OUTER  JOIN [dbo].[BGT1] T4  ON  T3.AcctCode = T0.AcctCode  AND  T0.Line_Id = 2
LEFT OUTER  JOIN [dbo].[BGT1] T5  ON  T3.AcctCode = T0.AcctCode  AND  T0.Line_Id = 3
GROUP BY T0.AcctCode

Best wishes

-Suda