April 18, 2012 at 11:47 pm
Hi,
I currently have a view with 5 columns. I want to create a 6th that has an average of number of sales per client. Below is an example of what I have now:
No ClientsDescriptionSales Avg SalesSales
10 Honda $347,149.50 $12,857.39 27
1 Ford $29,446.88 $4,907.81 6
I would like to add another column for the average of sales per client as per below
No ClientsDescriptionSales Avg SalesSales Avg per Client
10 Honda $347,149.50 $12,857.39 27 $34,714.95
1 Ford $29,446.88 $4,907.81 6 $29,446.88
Hopefully this makes sense and someone can help.
Kris
April 19, 2012 at 2:50 am
This was removed by the editor as SPAM
April 19, 2012 at 4:01 pm
You did not provide a script to create a table with sample data, so it makes much harder to answer your question.
Your example looks like you need a column with SalesTotal divided with number of clients:
SELECT SalesPerClient = SalesTotal / NoOfClients,
--...other columns...
FROM yourtable
No need for CTE, it cannot be used in views anyway.
Other options are nested query:
SELECT t2.*, newcol = t2.col1 / t2.col2
FROM
( SELECT col1, col2
FROM T1
) T2
Also, learn to use OVER() clause. It is very useful and often overlooked that it can be used with aggregate functions like SUM or AVG.
E.g.
SELECT AVG(Sales) OVER(PARTITION BY Customer), Sales, Customer
FROM yourtable
-- No GROUP BY HERE!
I can't give you more precise answer because you did not post the complete example (sql script).
Good luck!
April 20, 2012 at 1:31 am
This was removed by the editor as SPAM
April 20, 2012 at 2:00 am
Sorry, here is the script
SELECT COUNT(DISTINCT ClientName) AS NoClients, COUNT(desc) AS NumMtrInv, desc, SUM(sales) AS Fees, AVG(Fee) AS Avgsales
FROM dbo.VW_sales
GROUP BY desc
ORDER BY COUNT(DISTINCT NoName), Desc
Kris
April 20, 2012 at 2:18 am
Thanks Stewart, you are right. I don't know from where I concluded cte is not for view. Maybe sometime ago I tried that and it didn't worked, but don't know why or which version was that. Thanks again for enlightening me. 🙂
April 20, 2012 at 2:21 am
SELECT COUNT(DISTINCT ClientName) AS NoClients, COUNT(desc) AS NumMtrInv, desc, SUM(sales) AS Fees, AVG(Fee) AS Avgsales,
SUM(sales) / COUNT(DISTINCT ClientName) AS AvgSalesPerClient
FROM dbo.VW_sales
GROUP BY desc
ORDER BY COUNT(DISTINCT NoName), Desc
April 20, 2012 at 2:31 am
Thanks Vedran, That was perfect 😀
Kris
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply