SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

SQLNEWBIZ
Starting Member

India
26 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

Instead
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

allan8964
Posting Yak Master

247 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

visakh16
Very Important crosS Applying yaK Herder

India
52249 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
FROM
(
Select Inv_Cust,Count(Inv_Cust) as Salescount ,
DENSE_RANK() OVER (ORDER BY Count(Inv_Cust) DESC) AS Rnk
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
)t
WHERE rnk=1


Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000