• Status: Solved
• Priority: Medium
• Security: Public
• Views: 246

# Grouping and then then adding a total row

Hi

how

1) to Add a total row using rollup
2) order by date

count(*) as Number_of_transactions,
SUM(CASE WHEN amount > 0 THEN amount ELSE 0.0 END) AS [Credits],
SUM(CASE WHEN amount < 0 THEN amount ELSE 0.0 END) AS [Debits],
SUM(amount)AS [Total]
from ac_audit (nolock)
Group by
0
daz1234
• 7
• 6
1 Solution

Commented:
count(*) as Number_of_transactions,
SUM(CASE WHEN amount > 0 THEN amount ELSE 0.0 END) AS [Credits],
SUM(CASE WHEN amount < 0 THEN amount ELSE 0.0 END) AS [Debits],
SUM(amount)AS [Total]
from ac_audit (nolock)
Group by
ORDER BY current_timestamp DESC
0

Author Commented:
when using ORDER BY current_timestamp DESC

it list the dates as below

01/05/2005
02/05/2005
03/05/2005
04/05/2005
05/05/2005
06/05/2005
07/05/2005
08/05/2005
09/05/2005
10/05/2005
11/05/2005
12/05/2005
13/05/2005
14/05/2005
30/04/2005

but i need them to be listed as

30/04/2005
01/05/2005
02/05/2005
03/05/2005
04/05/2005
05/05/2005
06/05/2005
07/05/2005
08/05/2005
09/05/2005
10/05/2005
11/05/2005
12/05/2005
13/05/2005
14/05/2005
0

Author Commented:
and how do you add into the query

CASE
ELSE postdate
END AS postdate
0

Commented:
try

order by convert(datetime,job_date,103) desc

0

Commented:
count(*) as Number_of_transactions,
SUM(CASE WHEN amount > 0 THEN amount ELSE 0.0 END) AS [Credits],
SUM(CASE WHEN amount < 0 THEN amount ELSE 0.0 END) AS [Debits],
SUM(amount)AS [Total],
postdate_field=
CASE
WHEN (Grouping(Convert(varchar, adt_date, 103))=1) THEN 'Total'
ELSE postdate
END
from ac_audit (nolock)
Group by
ORDER BY convert(datetime,job_date,103) DESC
0

Author Commented:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'postdate'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'job_date'.

response when i run
0

Commented:
sorry i left so testing stuff in there - change the date fields to reprsent the dates you require
---------------------------------------------------------
count(*) as Number_of_transactions,
SUM(CASE WHEN amount > 0 THEN amount ELSE 0.0 END) AS [Credits],
SUM(CASE WHEN amount < 0 THEN amount ELSE 0.0 END) AS [Debits],
SUM(amount)AS [Total],
postdate_field=
CASE
WHEN (Grouping(Convert(varchar, adt_date, 103))=1) THEN 'Total'
END
from ac_audit (nolock)
Group by
0

Author Commented:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'ac_audit.ADT_DATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8127, Level 16, State 1, Line 1
Column name 'ac_audit.ADT_DATE' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
0

Commented:
count(*) as Number_of_transactions,
SUM(CASE WHEN amount > 0 THEN amount ELSE 0.0 END) AS [Credits],
SUM(CASE WHEN amount < 0 THEN amount ELSE 0.0 END) AS [Debits],
SUM(amount)AS [Total],
postdate_field=
CASE
WHEN (Grouping(Convert(varchar, adt_date, 103))=1) THEN 'Total'
END
from ac_audit (nolock)
Group by
0

Author Commented:
sorry getting same message

Server: Msg 8120, Level 16, State 1, Line 1
Column 'ac_audit.ADT_DATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'ac_audit.ADT_DATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0

Commented:
What is adt_date defined as ?
0

Author Commented:
adt_date is a column with datetime of transaction

but to list for a day by day and group i used Convert(datetime, adt_date, 103)
0

Commented:
Group by