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
 General SQL Server Forums
 New to SQL Server Programming
 Trouble in getting the total count() from query

Author  Topic 

SQLNEWBIZ
Starting Member

27 Posts

Posted - 2013-09-21 : 22:18:51
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

allan8964
Posting Yak Master

249 Posts

Posted - 2013-09-22 : 00:35:58
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 02:15:40
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


[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -