Grouping and then then adding a total row

Select Convert(varchar, adt_date, 103),
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)
where adt_date between dateadd (ww,-2,current_timestamp) and current_timestamp
Group by
       Convert(varchar, adt_date, 103) WITH ROLLUP
ORDER BY current_timestamp DESC

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

and how do you add into the query

  CASE
    WHEN (Grouping(Convert(varchar, adt_date, 103))=1) ad postdate THEN 'Total'
    ELSE postdate
  END AS postdate

try

order by convert(datetime,job_date,103) desc

to correct your order by

Select Convert(varchar, adt_date, 103),
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)
where adt_date between dateadd (ww,-2,current_timestamp) and current_timestamp
Group by
       Convert(varchar, adt_date, 103) WITH ROLLUP
ORDER BY convert(datetime,job_date,103) DESC

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

sorry i left so testing stuff in there - change the date fields to reprsent the dates you require
---------------------------------------------------------
Select Convert(varchar, adt_date, 103),
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 adt_date
END
from ac_audit (nolock)
where adt_date between dateadd (ww,-2,current_timestamp) and current_timestamp
Group by
       Convert(varchar, adt_date, 103) WITH ROLLUP
ORDER BY convert(datetime,adt_date,103) DESC

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.

Select Convert(datetime, adt_date, 103),
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 adt_date
END
from ac_audit (nolock)
where adt_date between dateadd (ww,-2,current_timestamp) and current_timestamp
Group by
       Convert(varchar, adt_date, 103) WITH ROLLUP
ORDER BY convert(datetime,adt_date,103) DESC

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.

What is adt_date defined as ?

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)