Group by Query

Hi,

Please help in writing a SQL query.
I have a table with EmpId,DirectSales,TeamLeaderId,TeamMemSales as some of the columns.The situation is
a Team Manager as sell directly, which comes under directsales and the overriding value of his team member comes under teammemsales.

Now I want get sum of both for a particular manager on a daily basis. Like

SaleDate sum(Directsales) sum(TeammemSales)

if the given id is empid then the it is directsales and if it is in teamleaderid then it is teammemsales.

How to achieve this task in query?

Any help is appreciated.

Thanks

M.L.SrinvasNot sure if I'm understanding here...but I think you just need an IF statement at the beginning of the procedure to test if the id is empid or TeamLeaderID. You can then run the appropriate select based on which ID was passed in.

GROUP BY In a GROUP BY

I dont want to get into too many details but I have a situation where I need information Aggregated, and then to have that information Aggregated again. So far I have been encapsulating all this within views but its gotten completely out of hand with too many views. So I was wondering if there was a way to make a group by within a group by?

Thanks in advance

Use derived table subqueries. This example will run in Northwind, and shows how many regions have a particular number of customers in them (eg. there are 13 regions that have one customer in them). I have highlighted the derived table, note that it is required to have an alias and its columns are required to have names or aliases too.

select CustomerCount, count(*) Regions
from
(select Region, count(*) as CustomerCount
from Customers
group by Region) CustomerCounts
group by CustomerCount
.

|||The derived table is not necessary, there are a couple of options depending on your scenarios.

If you need to display measures and subtotals on one column, you can use RollUp and Cube operators.

select region, country, sum(units) from sales group by country, region with

rollup

If you want to display them on separate columns e.g. to facilitate inter row calculation, you can use the Sql99 analytic function syntax i.e. partition by.

select region, country, sum(units) as regiontotal, sum(sum(units)) over (partition by country) as countrytotal from sales group by region, country

If you want to use a derived table, that's fine. But you have to be careful how to roll an aggregate up to the higher grain. E.g. for Count, you need to do SUM; for Avg, you need to do SUM / Count; it gets more complex for distinct aggregations.