create Table ##Sales (Agent Varchar(5) Null, DateNo int Null, Amount Money Null, Present char(1) Default 'N')
Select * from ##Sales
insert into ##Sales (Agent, DateNo, Amount, Present)
Select 'A', 2, 5000, 'Y' Union All
Select 'B', 3, 4000, 'Y' Union All
Select 'C', 4, 2000, 'N' Union All
Select 'D', 3, 10000, 'Y' Union All
Select 'E', 5, 5000, 'N' Union All
Select 'B', 4, 5000, 'N'
Please give Group by Query for Answer Should be :
Agent DateNo totalAmount PresentDays
A 1 5000.00 1
B 2 9000.00 1
C 1 2000.00 1
D 1 10000.00 1
E 1 5000.00 1
Select Agent, COUNT(DateNo) DateNo, SUM(Amount) Amount, count(Present) Present
from ##Sales
Group by Agent
Above query give output total Present Days but I want Total Amount according Agent and total Present Days