October 9, 2007 at 1:29 pm
This statement works great for what I am looking for, but I am trying to do a group by Site name, which is Sites.Sites. This column contains the location and I want to group it by that
Here are the tables
Employees
==============
EmployeesID
NetBIOSDomainName
UserID
FirstName
LastName
Alias
CollectorCode
Extension
SiteID
[DepartmentID
TitleID
LinkID
Sites
=====
SiteID
Sites
Departments
=============
DepartmentID
Departments
Titles
=======
TitlesID
Titles
Here is my SQL
select * from employees
left join departments
on employees.departmentID=departments.departmentID
left join sites
on employees.siteID=sites.siteID
left join Titles
On employees.titleID=titles.titlesID
But if I add GROUP BY sites.sites
I get this error for every column that I selected:
"Column is unvalid in the select list because it is not contained in either an aggragate function or the GROUP BY clause."
I am confused...
October 9, 2007 at 2:12 pm
Using a select * in conjunction with group by will usually cause that, since * can't be in the group by clause.
Are you sure you're not looking to use the order by clause instead? you have no aggregation going on in your query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 9, 2007 at 2:16 pm
I am not sure of the method I would want to use, group by or order by, but this is the output I am looking for:
Site1
=====
emp1
emp2
emp3
site2
======
emp4
emp5
emp6
Also I can remove the * and put all the column names in then do a MIN(employees.siteID)
What is your take?
October 9, 2007 at 2:39 pm
I think there's more here than meets the eye. Can you post a complete result set for what you are after? If you are truely after the results that you posted in your last post, the code below will do that, but based on your MIN() question, I think that we are missing something. It is important that, for us to help you, we know exactly what it is you are after. Especially when it comes to grouping/summing data and including non-aggregated columns in the SELECT.
SET NOCOUNT ON
DECLARE @Employees TABLE (
EmployeesID int,
NetBIOSDomainName varchar(15),
UserID varchar(8),
FirstName varchar(20),
LastName varchar(20),
Alias varchar(20),
CollectorCode int,
Extension int,
SiteID int,
DepartmentID int,
TitleID int,
LinkID int )
INSERT INTO @Employees (EmployeesID, UserID, SiteID)
SELECT 1, 'Emp1', 1 UNION ALL
SELECT 2, 'Emp2', 1 UNION ALL
SELECT 3, 'Emp3', 1 UNION ALL
SELECT 4, 'Emp4', 2 UNION ALL
SELECT 5, 'Emp5', 2 UNION ALL
SELECT 6, 'Emp6', 2
DECLARE @Sites TABLE (SiteID int, Sites varchar(20))
INSERT INTO @Sites
SELECT 1, 'Site 1' UNION ALL
SELECT 2, 'Site 2'
DECLARE @Departments TABLE (DepartmentID int, Departments varchar(20))
DECLARE @Titles TABLE (TitlesID int, Titles varchar(20))
SELECT UserID as 'Site 1'
FROM @employees e
LEFT JOIN @departments d
ON e.departmentID=d.departmentID
LEFT JOIN @sites s
ON e.siteID=s.siteID
LEFT JOIN @Titles t
ON e.titleID=t.titlesID
WHERE s.SiteID = 1
SELECT UserID as 'Site 2'
FROM @employees e
LEFT JOIN @departments d
ON e.departmentID=d.departmentID
LEFT JOIN @sites s
ON e.siteID=s.siteID
LEFT JOIN @Titles t
ON e.titleID=t.titlesID
WHERE s.SiteID = 2
October 9, 2007 at 2:43 pm
Are you looking for a blank record between groups? Are you trying to create a report object (as in a SSRS report) of some sort? Views or single select statements aren't going to create visual breaks like that (unless you "torture" things a like like John is) - that would usually be done in a report (crystal/access/SSRS) since that's a "visual" thing versus a data thing. Reporting applications have "grouping levels" allowing for group titles (group changes, and a new set of titles appear).
If you're NOT after the visual thing, try this on - see how close this gets you:
select *
from employees
left join departments
on employees.departmentID=departments.departmentID
left join sites
on employees.siteID=sites.siteID
left join Titles
On employees.titleID=titles.titlesID
Order by sitename,empname --assuming that's the name of the field for the employee name
If that doesn't do it for you - try actually mocking up /sketching out something approxmiating what you're looking for.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 9, 2007 at 2:56 pm
As has been mentioned I don't see any aggregation going on so there is no real reason for a group by. Also I notice you are using Left Outer Joins, which means that there is the possibility of the an employee not assigned to a department, department not assigned to a site, etc. If this is NOT the case then you should use Inner Joins. Also unless you are aggregating or there are duplicate records which you want you should just use the Order By suggested by Matt. Also as John said the results you posted that you were looking for are difficult to get using SQL. In SQL you are going to get records like this:
Site       Employee
-----       ---------
Site1      Employee1
Site1      Employee2
Site1      Employee3
Site2      Employee1
Site2      Employee2
Site3      Employee3
In order to see what you posted you would need to hack something together in SQL while Crystal, Access, SSRS, ActiveReports would handle that for you from the above results.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2007 at 3:50 pm
Matt Miller (10/9/2007)
Are you looking for a blank record between groups? Are you trying to create a report object (as in a SSRS report) of some sort? Views or single select statements aren't going to create visual breaks like that (unless you "torture" things a like like John is) - that would usually be done in a report (crystal/access/SSRS) since that's a "visual" thing versus a data thing. Reporting applications have "grouping levels" allowing for group titles (group changes, and a new set of titles appear).
Torture it is! I know that what I posted was not really what the OP wants (even though it is what they asked for) so it definately was torture for me to post that example. Sometimes it helps to give them exactly what they ask for to make them realize they are not asking for what they really want. 😀
October 9, 2007 at 3:56 pm
Right on! Careful what you ask for - you just might get it :hehe:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 9, 2007 at 5:30 pm
OK, here is the scoop. Boss says create a phone list for the company, but then he decides to create the DB. He made it difficult on me with the way he created the database with my limited knowledge. Can I get it done? Sure, but in a lot longer time span then someone that knows this in and out. I know enough about ASP and SQL to be extremely dangerous, but I am definately no pro. I love this stuff and want to expand my skill set (I am a windows admin).
The interface will be web based in ASP.NET.
So I want everything displayed in the employees table and the assigned values of title, department and site, but I wanted it grouped by like this
Site 1
========
emp1
emp2
Site2
=====
emp3
emp4
Also this is being created by scratch so I am not sure if this is an interface issue or not.
October 10, 2007 at 5:58 am
If you have SQL 2000 why not do it SSRS (SQL Server Reporting Services) which is an add-on included in your license. A phone list is a 20 minute report using SSRS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 10, 2007 at 6:14 am
Jack Corbett (10/10/2007)
If you have SQL 2000 why not do it SSRS (SQL Server Reporting Services) which is an add-on included in your license. A phone list is a 20 minute report using SSRS.
I cannot find SQL Server Reporting Services on our servers. Is it a seperate install?
October 10, 2007 at 6:41 am
yup - with 2000, yuo need to download it from MS download center separately (they were going to charge for it, and then stopped).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply