This is SQL Server CTE,
;with grp (Sdate,TransactionType,tot) as (select cast(CallStartTime as DATE)Date, Transaction_type as [Transaction type], (count ([Transaction_type])) as [Total Count] from TBL_AGENT_TRANSACTIONS where cast(CallStartTime as DATE)>='2015-04-27' and cast(CallEndTime as Date)<='2015-04-27' and Transaction_type in ('Debit_Card') group by CallStartTime, Transaction_type) select grp.Sdate,grp.TransactionType,sum(grp.tot) as totalCount from grp group by TransactionType,grp.Sdate,grp.tot
Here I want to group totalcount based on Sdate and Transaction Type. But it is not working correctly. I have 3 records with same Sdate, Transaction Type and I need to show totalcount as 3 as a single row. But i'm getting two rows as below.
Can any one help me to fix this minor issue?
I got the exact problem,
In my table 'TBL_AGENT_TRANSACTIONS', data type of the column 'Transaction_type' is varchar but I need to find the sum of that Transaction_type based on the Sdate and Transaction_type. If I put
sum(Transaction_type) in the first select query, it's showing datatype covertion failed error so I'm finding the
count(Transaction_type) in the first select query and getting sum of that field in the second select query. I guess my problem is in this process only.