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

Grouping and then then adding a total row

Hi

how

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

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)
0
daz1234
Asked:
daz1234
  • 7
  • 6
1 Solution
 
andrewbleakleyCommented:
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
0
 
daz1234Author 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
 
daz1234Author Commented:
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
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
andrewbleakleyCommented:
try

order by convert(datetime,job_date,103) desc

to correct your order by
0
 
andrewbleakleyCommented:
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
0
 
daz1234Author 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
 
andrewbleakleyCommented:
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
0
 
daz1234Author 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
 
andrewbleakleyCommented:
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
0
 
daz1234Author 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
 
andrewbleakleyCommented:
What is adt_date defined as ?
0
 
daz1234Author 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
 
andrewbleakleyCommented:
Group by
       Convert(varchar, adt_date, 103), adt_date WITH ROLLUP

should do it I tried it out here with success
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now