Please help in Sql query with Group by for given scenario

ajeet verma

11y1.3k

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Khrizz11y ago
Accepted answer
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'

Select * from ##Sales;
with cte(Agent, DateNo, Amount , Present)
AS
(
Select Agent, DateNo, Amount,
(Case Present WHen 'Y' THEN 1 ELSE 0 end) AS Present
from ##Sales
)
SELECT
Agent, count(DateNo) as DateNo, sum(Amount) as Amount, sum(Present) as Present
FROM cte
group by agent
truncate table ##Sales
drop table ##Sales
0
ajeet verma11y ago
thanks krizz
0
Suyambu Kesavan11y ago
Simply make a condition on where clause.

Select Agent, COUNT(DateNo) DateNo, SUM(Amount) Amount, count(Present) Present
from ##Sales "where Present = 'Y'"
Group by Agent
0
Raja T11y ago
Dear Ajeet,

As per my understanding, you want 1 as Present days weather the sales person is present or not .
Then why dont u try like ths:

Select Agent, COUNT(DateNo) DateNo, SUM(Amount) Amount, '1' as Present
from ##Sales Group by Agent

If my assumption is wrong, kindly explain me your requirement.

Thanks

0
ajeet verma11y ago
No,

the Output will be :

Agent Date 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

there is Sales person who got Amount wherever he is present or Not in Office
the result should be for Agent B is
B 2 9000.00 1
B is Present only one day but he got money 9000.00
0
Khan Abrar Ahmed11y ago
Hi try this

Select Agent, COUNT(DateNo) DateNo, SUM(Amount) Amount, count(Present) Present
from @Sales
where present='Y'
Group by Agent,Present
order by agent

0