Super Group by, or do I have to do multi qeuries? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Super Group by, or do I have to do multi qeuries?

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-03-18 : 00:20:07

Here is my table:

Table EmployeeSales
(
esID INT,
EmployeeID INT,
DepartmentID INT,
ProductID INT,
ClientID INT

Sales INT,
Commision INT

)

I want a summary of each section, namely EmployeeID, DepartmentID, ProductID and ClientID.

Desired OUTPUT (note, I can easily do INNER JOINS to get the Name etc)
----------------------------------------------------

Employees
--------
EmployeName Sales Commission

Joe 5000 500
Mark 1200 200

etc.

Departments
---------------

DepartmentName Sales Commission
NorthWing 5050 120
SouthWIng 4545 525

Products
-----------
ProductName Sales Commission

etc
etc.

Is this all possible with ONE query or do I have to do many queries for each section?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-18 : 02:56:04

This is possible using UNION - because all of the different results have 3 columns, but the only problem will be that the column Names will have to be the same throughout, so instead of 'ProductName' and 'DepartmentName' you'll probably just have to use 'Name'.

Look up UNION in Books On Line.

Duane.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-18 : 07:48:41

it looks to me like you have a presention issue, not a sql issue. how are you outputting this data?

- Jeff

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-03-18 : 10:44:13

using dotnet on a webpage. Is that what you mean?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-18 : 11:11:32

it's just 3 different queries, or a stored proc that returns 3 recordset. process each one and display them, one at a time.

Do you need help with writing the 3 queries, with the GROUP BY's and the SUM() aggregate function?

- Jeff

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-03-18 : 11:25:14

Jeff,

yes in fact I do. If you can do 1 for me, I can figure out the rest. I just thought MAYBE it could be done with 1 query, guess not!

Thanks!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-18 : 11:43:07

each one would look like this:

select employeeID, sum(sales) as TotalSales, sum(Commision) as TotalCommision
from EmployeeSales
group by employeeID

if you want them all in 1 recordset, you can do something like this:

select 'Employee' as Type, employeeID, sum(sales) as TotalSales, sum(Commision) as TotalCommision
from EmployeeSales
group by employeeID
union all
select 'Department' as Type, DepartmentID, sum(sales) as TotalSales, sum(Commision) as TotalCommision
from EmployeeSales
group by DepartmentID
union all
select 'Client' as Type, ClientID, sum(sales) as TotalSales, sum(Commision) as TotalCommision
from EmployeeSales
group by ClientID
...etc ....

or something like that. experiment .... read about GROUP BY in books on line, the SUM() function, and also UNION and UNION ALL.

- Jeff

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-03-18 : 15:58:52

Thanks Jeff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources