Count Number of Customers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Count Number of Customers

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2011-04-14 : 10:03:03

hi

This is my table
Date ClientID Amount
11/04 1 $10
11/04 1 $5
11/04 2 $15
11/04 3 $5
11/04 4 $2
11/04 5 $10
12/04 1 $3
12/04 2 $5
12/04 2 $10
12/04 3 $5
12/04 3 $5
12/04 5 $10

I would like to count number of clients based on the amount spent for any given day. For example, i want to find out how many client spent on a given of more than or equal to $10 between 11/04 to 12/04

The result should look like this

Date Count
11/04 3
12/04 3

How should i go about this? Thanks

singularity
Posting Yak Master

153 Posts

Posted - 2011-04-14 : 10:14:27

[code]
select Date, count(ClientID) as [Count]
from
(select Date, ClientID, sum(Amount) as Amount
from yourtable
where Date between '11/04/2011' and '12/04/2011'
group by Date, ClientID
having sum(Amount) >= 10) a
group by Date
[/code]

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-14 : 13:04:49

The sum(amount) can be omitted from column list in subquery.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources