-1

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.

enter image description here

Can any one help me to fix this minor issue?

Update:

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.

closed as off-topic by Paul White, Colin 't Hart, Philᵀᴹ, dezso, RLF Apr 28 '15 at 12:49

If this question can be reworded to fit the rules in the help center, please edit the question.

  • You have forget to add "grp.TransactionType" in second select statement. – Md Haidar Ali Khan Apr 28 '15 at 6:15
  • Yes. But I modified it and there is no change in the result. – Sathiya Kumar Apr 28 '15 at 6:17
4

add cast(CallStartTime as DATE) in your GROUP BY:

;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 cast(CallStartTime as DATE), Transaction_type)

select grp.Sdate,grp.TransactionType,sum(grp.tot) as totalCount 
from grp 
group by TransactionType,grp.Sdate,grp.tot

Not the answer you're looking for? Browse other questions tagged or ask your own question.