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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trouble in getting the total count() from query
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

27 Posts

Posted - 09/21/2013 :  22:18:51  Show Profile  Reply with Quote
Hi All,
I have to write a query to get the count() of the customer who has max sales in the last 6 months.
my query is
Select Inv_Cust,Count(Inv_Cust) as Salescount From Inv_Header Group By Inv_Cust,Inv_Date Having Inv_Date Between MIN(Inv_Date) And DATEADD(MM,6,min(Inv_Date))

which gives me a result like
inv_cust ' Salescount
BO1 1
C01 1
A01 1
B01 1
B01 1

I want it as

Inv_Cust Salescount
B01 3
A01 1
C01 1

How can I modify my existing query to get this..
Any help will be appreciated

Edited by - SQLNEWBIZ on 09/21/2013 23:19:58

Posting Yak Master

249 Posts

Posted - 09/22/2013 :  00:35:58  Show Profile  Reply with Quote
I did not see any info on max sales so I assume you consider that in the scripts.
Nothing is wrong with codes. This depends what you want to show. If you just want to show (1) all customers within some 6 months and (2) no customer have sales on the same days then try this:

Select Inv_Cust,Count(Inv_Cust) as Salescount From Inv_Header
Where Inv_Date between 'your min date' and DATEADD(MM, 6, 'your min date')
Group By Inv_Cust

Here 'your min date' looks like '2013-03-22'.

If you put Inv_Date join the group then it will try to match Inv_Cust .... the result will show all rows. If you have lot of rows there and a customer may have sales in same day then you may use Inv_Date in group as you wrote. Why you see

inv_cust ' Salescount
BO1 1
C01 1
A01 1
B01 1
B01 1

is because you don't have record for any of them have sales on same date. Keep add more rows and make, for example BO1 with '2013-03-23' twice or more then run your original scripts. You will see something as you expect.

Edited by - allan8964 on 09/22/2013 00:40:42
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 09/22/2013 :  02:15:40  Show Profile  Reply with Quote
i think what you need is something like this

SELECT Inv_Cust,Salescount
Select Inv_Cust,Count(Inv_Cust) as Salescount ,
From Inv_Header 
where Inv_Date >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-6,0)
AND Inv_Date < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
Group By Inv_Cust
WHERE rnk=1

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000