Grouping Sorting String and Numerical Fields

I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.

Patient Date Albumin
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9

However, when I try to group by Albumin, Patient, it just sorts by Albumin.
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Adams, John 01/28/2007 4.1
Barker, Mark 01/22/2007 4.1
Adams, John 12/30/2007 3.9
Barker, Mark 01/05/2007 3.9
Adams, John 01/15/2007 3.2

What I'm looking for is this:
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2

Is this something that can be done with grouping and sorting?

Thanks,
Chad

Hi,

guess that you did not want to group you wanted to just sort, right ? For getting the results pasted below you will have to Sort by Patient Desc, Albumin Desc. If that is not your intention, please post the RDL and the way you want to display the information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Grouping Results (Summarizing)

I can't figure out how to take display my results like so...
NJ
--Trenton, John Doe
--Voorhees, Jane Smith
Pennsylvania
--Philadelphia, Ken James
--Pittsburgh, Alfred Herms
NY
--Brooklyn, Llyod Banks
--Syracuse, Howard Douglas
within a SQL statement..<vncntj@.hotmail.com> wrote in message
news:1140727759.857021.104520@.v46g2000cwv.googlegroups.com...
>I can't figure out how to take display my results like so...
>
> NJ
> --Trenton, John Doe
> --Voorhees, Jane Smith
> Pennsylvania
> --Philadelphia, Ken James
> --Pittsburgh, Alfred Herms
> NY
> --Brooklyn, Llyod Banks
> --Syracuse, Howard Douglas
> within a SQL statement..
>
Since you haven't told us what the table structure is, what your original
data looks like or what version of SQL Server you are are using I don't
think I can figure it out either. Read my signature.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||CREATE TABLE dbo.Table1
(
state nvarchar(50) NULL,
city nvarchar(50) NULL,
name nvarchar(50) NULL
)
INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
Doe')
INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
Smith')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PHILADELPHIA', 'Ken Jame')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PITTSBURGH', 'Alfred Herms')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
'Lloyd Banks')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
'Howard Douglas')
Is there a way to present the data as so...
NJ
-Trenton
-Voorhees
Pennsylvania
-Philadelphia
-Pittsburgh
NY
-Brooklyn
-Syracuse|||vncntj@.hotmail.com wrote:
> CREATE TABLE dbo.Table1
> (
> state nvarchar(50) NULL,
> city nvarchar(50) NULL,
> name nvarchar(50) NULL
> )
> INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
> Doe')
> INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
> Smith')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PHILADELPHIA', 'Ken Jame')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PITTSBURGH', 'Alfred Herms')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
> 'Lloyd Banks')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
> 'Howard Douglas')
> Is there a way to present the data as so...
> NJ
> -Trenton
> -Voorhees
> Pennsylvania
> -Philadelphia
> -Pittsburgh
> NY
> -Brooklyn
> -Syracuse
Given your original table any reporting tool will output data with
formatted bands like that. So unless you want to print or display
direct from your query tool it seems like it would be very inconvenient
to do all that formatting in a result set. SQL isn't a report writing
tool.
If you really have no other option then you could do something like
this:
SELECT s
FROM
(SELECT state, 1, state
FROM Table1
UNION
SELECT state, 2, '-- '+city
FROM Table1) AS T(t,o,s)
ORDER BY t,o ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Grouping Results (Summarizing)

I can't figure out how to take display my results like so...
NJ
--Trenton, John Doe
--Voorhees, Jane Smith
Pennsylvania
--Philadelphia, Ken James
--Pittsburgh, Alfred Herms
NY
--Brooklyn, Llyod Banks
--Syracuse, Howard Douglas
within a SQL statement..
<vncntj@.hotmail.com> wrote in message
news:1140727759.857021.104520@.v46g2000cwv.googlegr oups.com...
>I can't figure out how to take display my results like so...
>
> NJ
> --Trenton, John Doe
> --Voorhees, Jane Smith
> Pennsylvania
> --Philadelphia, Ken James
> --Pittsburgh, Alfred Herms
> NY
> --Brooklyn, Llyod Banks
> --Syracuse, Howard Douglas
> within a SQL statement..
>
Since you haven't told us what the table structure is, what your original
data looks like or what version of SQL Server you are are using I don't
think I can figure it out either. Read my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||CREATE TABLE dbo.Table1
(
state nvarchar(50) NULL,
city nvarchar(50) NULL,
name nvarchar(50) NULL
)
INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
Doe')
INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
Smith')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PHILADELPHIA', 'Ken Jame')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PITTSBURGH', 'Alfred Herms')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
'Lloyd Banks')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
'Howard Douglas')
Is there a way to present the data as so...
NJ
-Trenton
-Voorhees
Pennsylvania
-Philadelphia
-Pittsburgh
NY
-Brooklyn
-Syracuse
|||vncntj@.hotmail.com wrote:
> CREATE TABLE dbo.Table1
> (
> state nvarchar(50) NULL,
> city nvarchar(50) NULL,
> name nvarchar(50) NULL
> )
> INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
> Doe')
> INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
> Smith')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PHILADELPHIA', 'Ken Jame')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PITTSBURGH', 'Alfred Herms')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
> 'Lloyd Banks')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
> 'Howard Douglas')
> Is there a way to present the data as so...
> NJ
> -Trenton
> -Voorhees
> Pennsylvania
> -Philadelphia
> -Pittsburgh
> NY
> -Brooklyn
> -Syracuse
Given your original table any reporting tool will output data with
formatted bands like that. So unless you want to print or display
direct from your query tool it seems like it would be very inconvenient
to do all that formatting in a result set. SQL isn't a report writing
tool.
If you really have no other option then you could do something like
this:
SELECT s
FROM
(SELECT state, 1, state
FROM Table1
UNION
SELECT state, 2, '-- '+city
FROM Table1) AS T(t,o,s)
ORDER BY t,o ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Grouping Results (Summarizing)

I can't figure out how to take display my results like so...
NJ
--Trenton, John Doe
--Voorhees, Jane Smith
Pennsylvania
--Philadelphia, Ken James
--Pittsburgh, Alfred Herms
NY
--Brooklyn, Llyod Banks
--Syracuse, Howard Douglas
within a SQL statement..<vncntj@.hotmail.com> wrote in message
news:1140727759.857021.104520@.v46g2000cwv.googlegroups.com...
>I can't figure out how to take display my results like so...
>
> NJ
> --Trenton, John Doe
> --Voorhees, Jane Smith
> Pennsylvania
> --Philadelphia, Ken James
> --Pittsburgh, Alfred Herms
> NY
> --Brooklyn, Llyod Banks
> --Syracuse, Howard Douglas
> within a SQL statement..
>
Since you haven't told us what the table structure is, what your original
data looks like or what version of SQL Server you are are using I don't
think I can figure it out either. Read my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||CREATE TABLE dbo.Table1
(
state nvarchar(50) NULL,
city nvarchar(50) NULL,
name nvarchar(50) NULL
)
INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
Doe')
INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
Smith')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PHILADELPHIA', 'Ken Jame')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PITTSBURGH', 'Alfred Herms')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
'Lloyd Banks')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
'Howard Douglas')
Is there a way to present the data as so...
NJ
-Trenton
-Voorhees
Pennsylvania
-Philadelphia
-Pittsburgh
NY
-Brooklyn
-Syracuse|||vncntj@.hotmail.com wrote:
> CREATE TABLE dbo.Table1
> (
> state nvarchar(50) NULL,
> city nvarchar(50) NULL,
> name nvarchar(50) NULL
> )
> INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
> Doe')
> INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
> Smith')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PHILADELPHIA', 'Ken Jame')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PITTSBURGH', 'Alfred Herms')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
> 'Lloyd Banks')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
> 'Howard Douglas')
> Is there a way to present the data as so...
> NJ
> -Trenton
> -Voorhees
> Pennsylvania
> -Philadelphia
> -Pittsburgh
> NY
> -Brooklyn
> -Syracuse
Given your original table any reporting tool will output data with
formatted bands like that. So unless you want to print or display
direct from your query tool it seems like it would be very inconvenient
to do all that formatting in a result set. SQL isn't a report writing
tool.
If you really have no other option then you could do something like
this:
SELECT s
FROM
(SELECT state, 1, state
FROM Table1
UNION
SELECT state, 2, '-- '+city
FROM Table1) AS T(t,o,s)
ORDER BY t,o ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Grouping feilds

This should be fairly easy by my reckoning but i just can't seem to figure it out.

I'm fairly new on crystal reports so i'm learning on the fly as i do this and i have my boss asking me if it's ready hehe

On the screenshot attached all i want to do is group if their are multiple records and have the services display for that particular record.

I've attached the actual report as well if anyone get's chance to look at it. If i put the feilds in the details section and try and group the way i want it just all goes haywire.

Any ideas?

thanksthe report even with group will still show the name... this is because your Add on Services is different...|||I could suggest you create groups to records are shown again,
so they just would be shown one time.

sample
group for

File# Surname Fisrtname AsignStatus
------------
15498 Peter Jorn Active
Add on Services
-----
Global Temo Living
Visa & Inmigration

File# Surname Fisrtname AsignStatus
------------
1998 Name 1 Name2 Active
Add on Services
-----
Service1
Service2|||The other thing you can do is compare the File, SurName, FirstName, Asgn Status by checking all them with their previous values. If all are same then suppress them.|||Many thanks for the tips, i managed to get it working sort of similar to hensa22's example. The tricky bit with with crystal reports i'm finding is displaying the d@.mn data in a decent way hehe

--edit--
here is the final form if anyone does want to see itsql

Grouping Data with Weekly break out?

Hi All,
I'm trying to figure out and learn how to do the following:
/*I have the following query which works:*/
SELECT DataSource AS [Service Line], NamePrep AS [PO Created By],
COUNT(PoNumber) AS [Total Of PONumber]
FROM OPW
WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
GROUP BY DataSource, NamePrep
My question here is how can I get a wly breakout of the COUNT(PoNumber)?
I want a column for each w and the number of PONumbers Per Datasource and
NamePrep for that w.
Example Output:
Service Line | PO Created By | Total Of PONumber | 3/5/2005 |
3/12/2005 | ETC...
MNS JOHN 10
MNS ERIC 25
FMS CARL 8
Hope my question makes sense :)
John.If you had a calendar table, this would be easier, but if not, you need to
know the columns you want, or use dynamic SQL...
Select DataSource AS [Service Line], NamePrep AS [PO Created By],
COUNT(PoNumber) AS [Total Of PONumber],
Sum(Case When ReqSubmitDate
Between '03/ 01/2005' AND '03/8/2005' Then 1 End) Wk1Count,
Sum(Case When ReqSubmitDate
Between '03/ 09/2005' AND '03/16/2005' Then 1 End) Wk2Count,
Sum(Case When ReqSubmitDate
Between '03/ 17/2005' AND '03/24/2005' Then 1 End) Wk3Count,
Sum(Case When ReqSubmitDate
Between '03/ 25/2005' AND '03/31/2005' Then 1 End) Wk4Count
FROM OPW
WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
GROUP BY DataSource, NamePrep
If you want it dynamic, you have to write code to dynamic construct an SQL
statement like the one above, based on the date ranges you pass it, and then
execute that SQL Statement using EXECUTE, or sp_ExecuteSQL() functions
"John Rugo" wrote:

> Hi All,
> I'm trying to figure out and learn how to do the following:
> /*I have the following query which works:*/
> SELECT DataSource AS [Service Line], NamePrep AS [PO Created By],
> COUNT(PoNumber) AS [Total Of PONumber]
> FROM OPW
> WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
> GROUP BY DataSource, NamePrep
> My question here is how can I get a wly breakout of the COUNT(PoNumber)
?
> I want a column for each w and the number of PONumbers Per Datasource a
nd
> NamePrep for that w.
> Example Output:
> Service Line | PO Created By | Total Of PONumber | 3/5/2005
|
> 3/12/2005 | ETC...
> MNS JOHN 10
> MNS ERIC 25
> FMS CARL 8
> Hope my question makes sense :)
> John.
>
>sql

grouping by partial string

Hello All,

I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...

Bob001

Bob

Robert005

Doug053

Doug100

Douglas

Barney001

Frank

I want to group it up as...

Bob

Doug

Barney

Frank

And then be able to summarize the results in the matrix.

Thanks in advance for any help

-Clint

If the last 3 characters are always 3 digits then you can simply group by left(Name, len(Name) - 3)

Adamus

|||

Just be sure your SELECT left(Name, len(Name) - 3) contains the trim.

Adamus

|||Unfortunately they do not all end in 3 digits. Ill fix my example. thanks though|||

Ok next question:

If digits exist, will they always be 3 digits on the tail end of the name?

If so you can check CASE Name WHEN isnumeric(right(Name, 3)) ...

If not, you'll probably want to use regular expressions to grab the text only portion.

In that case, you'll have to create a UDF which you can find here

Adamus

|||

Thanks Adamus,

Yes, when there are numbers present at the end, there are always three.

What would the expression look like?

Thanks much

Clint

|||

CREATETABLE #temp(myName varchar(20))

INSERTINTO #temp SELECT'Bob001'

INSERTINTO #temp SELECT'Bob'

INSERTINTO #temp SELECT'Robert001'

INSERTINTO #temp SELECT'Robert'

SELECT*FROM

(

SELECTCASEWHENISNUMERIC(RIGHT(myName, 3))= 1

THENLEFT(myName,LEN(myName)- 3)ELSE myName ENDAS [Result]

FROM #temp

)as t

GROUPBY Result

DROPTABLE #temp

Adamus

|||

What happened to Robert in the second list? Was he fired? Did he quit?

On A serious note, someone posted the code you need to remove the digits...

though it does not appear that it will solve the problem as "Douglas" has no digits on the end of it.

What you are looking for, sounds to me is some type of pattern matching...

|||

nice touch.. Mr. Turner...

I hope he actually uses the correct sql to do a select into the temp table instead of doing it all literally.

|||Thanks for the help. I am trying to accomplish in reporting services itself, not in the dbase. From what I gather there is now expression that would do the same thing. Again,I appreciate the help.|||

You can do this in SQL in the DataSet instead of the database or you can do in an expression using the Len function to find out how long the initial string is, the InStr function to find the location of a number, and then the Mid function to extract a string that is the Total Length long minus the Index value of the number that was found. Wrap all this in an IIF to handle the cases when no number is present.

HTH

Dean

|||

After regrouping at another field that seperated out the fields ending in - 083, i was able to get it working with your expression modified. Thanks so much for your help. Here is what ended up working...

Code Snippet

=iif(Fields!BankNumber.Value="083",left(Fields!TestName.Value, len(Fields!TestName.Value) - 11),Fields!TestName.Value)

|||oops. turns out not to work quite right after all. I errors out the first field name but works for the rest. I get a warning saying that the Length statement must be equal to or greater than 0. Since we are using a negative number, it is popping a warning.|||

Exactly. Im trying to figure out how to trim the last 11 characters off certain fields. My real life example has some fields ending in - Bank 083 (or another 3 numbers). i am thinking of the "LIKE" to seach for the pattern and remove it for group sake. Anyone have an idea on that one?

Thanks,

Clint

Group Total Not working right

Hi,

I have a problem that i cannot figure out how to fix.

I have a sub report that i need to have the group totals in the Page header and i cannot for the life of me remember how to do this.

I have grouped by field 2 which gives me a time for Planned and unplanned events, I need to add up the time of the Planned items and put next to my Downtime Planned Text Box, and then sum the Unplanned and do the same against my unplanned downtime box

Regards SteveExample:
subreport 1 formula:
whileprintingrecords;
shared numbervar x:= sum({table.field})
subreport 2 formula:
whileprintingrecords;
shared numbervar y:= sum({table.field})
Formula in the main report:
whileprintingrecords;
shared numbervar x;
shared numbervar y;
x+y|||just to add some thing. You can use the sum function as

sum({table.field}, "Group on the field");

So you can get the planned and unplanned in the same subreport.|||Very confused with First reply, but will have a go at that, Second reply worked fine fo the same report

Regards

Steve

Group Count

Good Morning.

This should be fairly easy but for the life of me, I can't figure it out...

I am using SQL reporting Services 2000 and Visual Studio 2003.

I have a very simple report that is grouped by a users name. What I can't figure out is how to get the count of records in a group. I have already got the total number of records for all groups but I also need to have the Group count as well.

Any help would be greatly apprceiated

Try the inscope function?

http://devauthority.com/blogs/chrisslatt/archive/2005/07/06/Reporting_Service_Using_InScope_to_do_custom_subtotals.aspx

|||

For table like this:

Table Header:
Group Header:
Detail:
Group Footer:
Table Footer:

I do the following: add COUNT() into Group Footer line. It will calculate you number of rows for each group. If you put COUNT to Table Footer it will calculate you total number of rows. It's for simple situation. In case of complicated calculation and table structure you can use InScope, as Andrew told you.

GROUP BY Question

Given the following tables, I would like to figure out employee (employeeID)
leads the most assignments.
Doing it in two steps works (after a fashion) with a view (as listed below)
but I would like the Assignmentleader ID and the number of assignments that
person leads only for the leader that leads the most assignments and do it
in one query if possible.
CREATE VIEW MostNum AS
SELECT AssignmentLeader,COUNT(AssignmentID) AS NumOF
FROM Assignments
GROUP BY AssignmentLeader
CREATE TABLE Employees
(
EmployeeID INT NOT NULL PRIMARY KEY,
FirstName CHAR (20) NULL,
LastName CHAR (20) NULL,
HireDate DATETIME NULL,
TerminationDate DATETIME NULL,
EmployeeComments CHAR (300) NULL,
SupervisorsID INT NULL,
FOREIGN KEY (SupervisorsID) REFERENCES Employees (EmployeeID)
)
--Employees sample data
INSERT INTO Employees VALUES(101,'James','Hinkle','2006-01-01
00:00:00.000',NULL,'The Big Boss',NULL)
INSERT INTO Employees VALUES(102,'Dave','McCracken','2006-01-04
00:00:00.000',NULL,'VP',101)
INSERT INTO Employees VALUES(103,'Phil','Jensen','2006-01-05
00:00:00.000',NULL,'Accounting Supervisor',101)
INSERT INTO Employees VALUES(104,'Melinda','Carol','2006-01-05
00:00:00.000',NULL,'Finance Supervisor',101)
INSERT INTO Employees VALUES(105,'Daniel','Humphreys','2006-01-06
00:00:00.000',NULL,'MIS Support',101)
INSERT INTO Employees VALUES(106,'Lisa','Nugen','2006-01-06
00:00:00.000',NULL,'Shipping Supplier',101)
INSERT INTO Employees VALUES(107,'Henry','Wallace','2006-01-06
00:00:00.000',NULL,'Shipping PC',106)
INSERT INTO Employees VALUES(108,'Karen','Berrett','2006-01-07
00:00:00.000',NULL,'Secretary',103)
INSERT INTO Employees VALUES(109,'Ben','Pratt','2006-01-08
00:00:00.000','2006-01-09 00:00:00.000','Grunt',104)
INSERT INTO Employees VALUES(110,'Chump','Wegion','2006-01-08
00:00:00.000',NULL,'Warehouse PC',101)
CREATE TABLE Assignments
(
AssignmentID INT NOT NULL PRIMARY KEY,
AssignmentDescription CHAR(60) NULL ,
AssignmentLeader INT NULL
FOREIGN KEY (AssignmentLeader) REFERENCES Employees (EmployeeID)
)
--Assignments sample data
INSERT INTO Assignments VALUES(1,'Run operations of company',105)
INSERT INTO Assignments VALUES(2,'East Coast Marketing Plan',101)
INSERT INTO Assignments VALUES(3,'West Coast Marketing Plan',103)
INSERT INTO Assignments VALUES(4,'Build a new assembly line for Mark V',101)
INSERT INTO Assignments VALUES(5,'Research and develop a new pump
sprayer',102)
INSERT INTO Assignments VALUES(6,'Build new engineering facility',109)SELECT TOP 1 EmployeeID, COUNT(*) FROM Assignments GROUP BY EmployeeID ORDER
BY 2 DESC
"David Olsen" <david.olsen@.usu.edu> wrote in message
news:%23Dn2%23n7QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Given the following tables, I would like to figure out employee
> (employeeID) leads the most assignments.
>
> Doing it in two steps works (after a fashion) with a view (as listed
> below) but I would like the Assignmentleader ID and the number of
> assignments that person leads only for the leader that leads the most
> assignments and do it in one query if possible.
>
>
>
> CREATE VIEW MostNum AS
>
> SELECT AssignmentLeader,COUNT(AssignmentID) AS NumOF
> FROM Assignments
> GROUP BY AssignmentLeader
>
>
> CREATE TABLE Employees
> (
> EmployeeID INT NOT NULL PRIMARY KEY,
> FirstName CHAR (20) NULL,
> LastName CHAR (20) NULL,
> HireDate DATETIME NULL,
> TerminationDate DATETIME NULL,
> EmployeeComments CHAR (300) NULL,
> SupervisorsID INT NULL,
>
> FOREIGN KEY (SupervisorsID) REFERENCES Employees (EmployeeID)
>
> )
>
>
> --Employees sample data
> INSERT INTO Employees VALUES(101,'James','Hinkle','2006-01-01
> 00:00:00.000',NULL,'The Big Boss',NULL)
> INSERT INTO Employees VALUES(102,'Dave','McCracken','2006-01-04
> 00:00:00.000',NULL,'VP',101)
> INSERT INTO Employees VALUES(103,'Phil','Jensen','2006-01-05
> 00:00:00.000',NULL,'Accounting Supervisor',101)
> INSERT INTO Employees VALUES(104,'Melinda','Carol','2006-01-05
> 00:00:00.000',NULL,'Finance Supervisor',101)
> INSERT INTO Employees VALUES(105,'Daniel','Humphreys','2006-01-06
> 00:00:00.000',NULL,'MIS Support',101)
> INSERT INTO Employees VALUES(106,'Lisa','Nugen','2006-01-06
> 00:00:00.000',NULL,'Shipping Supplier',101)
> INSERT INTO Employees VALUES(107,'Henry','Wallace','2006-01-06
> 00:00:00.000',NULL,'Shipping PC',106)
> INSERT INTO Employees VALUES(108,'Karen','Berrett','2006-01-07
> 00:00:00.000',NULL,'Secretary',103)
> INSERT INTO Employees VALUES(109,'Ben','Pratt','2006-01-08
> 00:00:00.000','2006-01-09 00:00:00.000','Grunt',104)
> INSERT INTO Employees VALUES(110,'Chump','Wegion','2006-01-08
> 00:00:00.000',NULL,'Warehouse PC',101)
>
>
> CREATE TABLE Assignments
> (
> AssignmentID INT NOT NULL PRIMARY KEY,
> AssignmentDescription CHAR(60) NULL ,
> AssignmentLeader INT NULL
> FOREIGN KEY (AssignmentLeader) REFERENCES Employees (EmployeeID)
> )
>
> --Assignments sample data
> INSERT INTO Assignments VALUES(1,'Run operations of company',105)
> INSERT INTO Assignments VALUES(2,'East Coast Marketing Plan',101)
> INSERT INTO Assignments VALUES(3,'West Coast Marketing Plan',103)
> INSERT INTO Assignments VALUES(4,'Build a new assembly line for Mark
> V',101)
> INSERT INTO Assignments VALUES(5,'Research and develop a new pump
> sprayer',102)
> INSERT INTO Assignments VALUES(6,'Build new engineering facility',109)
>
>|||"David Olsen" <david.olsen@.usu.edu> wrote in message
news:%23Dn2%23n7QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Given the following tables, I would like to figure out employee
> (employeeID) leads the most assignments.
>
> Doing it in two steps works (after a fashion) with a view (as listed
> below) but I would like the Assignmentleader ID and the number of
> assignments that person leads only for the leader that leads the most
> assignments and do it in one query if possible.
>
>
>
> CREATE VIEW MostNum AS
>
> SELECT AssignmentLeader,COUNT(AssignmentID) AS NumOF
> FROM Assignments
> GROUP BY AssignmentLeader
>
>
> CREATE TABLE Employees
> (
> EmployeeID INT NOT NULL PRIMARY KEY,
> FirstName CHAR (20) NULL,
> LastName CHAR (20) NULL,
> HireDate DATETIME NULL,
> TerminationDate DATETIME NULL,
> EmployeeComments CHAR (300) NULL,
> SupervisorsID INT NULL,
>
> FOREIGN KEY (SupervisorsID) REFERENCES Employees (EmployeeID)
>
> )
>
>
> --Employees sample data
> INSERT INTO Employees VALUES(101,'James','Hinkle','2006-01-01
> 00:00:00.000',NULL,'The Big Boss',NULL)
> INSERT INTO Employees VALUES(102,'Dave','McCracken','2006-01-04
> 00:00:00.000',NULL,'VP',101)
> INSERT INTO Employees VALUES(103,'Phil','Jensen','2006-01-05
> 00:00:00.000',NULL,'Accounting Supervisor',101)
> INSERT INTO Employees VALUES(104,'Melinda','Carol','2006-01-05
> 00:00:00.000',NULL,'Finance Supervisor',101)
> INSERT INTO Employees VALUES(105,'Daniel','Humphreys','2006-01-06
> 00:00:00.000',NULL,'MIS Support',101)
> INSERT INTO Employees VALUES(106,'Lisa','Nugen','2006-01-06
> 00:00:00.000',NULL,'Shipping Supplier',101)
> INSERT INTO Employees VALUES(107,'Henry','Wallace','2006-01-06
> 00:00:00.000',NULL,'Shipping PC',106)
> INSERT INTO Employees VALUES(108,'Karen','Berrett','2006-01-07
> 00:00:00.000',NULL,'Secretary',103)
> INSERT INTO Employees VALUES(109,'Ben','Pratt','2006-01-08
> 00:00:00.000','2006-01-09 00:00:00.000','Grunt',104)
> INSERT INTO Employees VALUES(110,'Chump','Wegion','2006-01-08
> 00:00:00.000',NULL,'Warehouse PC',101)
>
>
> CREATE TABLE Assignments
> (
> AssignmentID INT NOT NULL PRIMARY KEY,
> AssignmentDescription CHAR(60) NULL ,
> AssignmentLeader INT NULL
> FOREIGN KEY (AssignmentLeader) REFERENCES Employees (EmployeeID)
> )
>
> --Assignments sample data
> INSERT INTO Assignments VALUES(1,'Run operations of company',105)
> INSERT INTO Assignments VALUES(2,'East Coast Marketing Plan',101)
> INSERT INTO Assignments VALUES(3,'West Coast Marketing Plan',103)
> INSERT INTO Assignments VALUES(4,'Build a new assembly line for Mark
> V',101)
> INSERT INTO Assignments VALUES(5,'Research and develop a new pump
> sprayer',102)
> INSERT INTO Assignments VALUES(6,'Build new engineering facility',109)
>
>
Transact-SQL proprietary version:
SELECT TOP 1 WITH TIES
assignmentleader, COUNT(*) AS numof
FROM Assignments
GROUP BY assignmentleader
ORDER BY COUNT(*) DESC ;
ANSI/ISO Standard SQL version:
SELECT assignmentleader, COUNT(*) AS numof
FROM Assignments
GROUP BY assignmentleader
HAVING COUNT(*)>= ALL
(SELECT COUNT(*)
FROM Assignments
GROUP BY assignmentleader);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23hw5rz7QGHA.1096@.TK2MSFTNGP11.phx.gbl...
> "David Olsen" <david.olsen@.usu.edu> wrote in message
> news:%23Dn2%23n7QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Transact-SQL proprietary version:
> SELECT TOP 1 WITH TIES
> assignmentleader, COUNT(*) AS numof
> FROM Assignments
> GROUP BY assignmentleader
> ORDER BY COUNT(*) DESC ;
>
> ANSI/ISO Standard SQL version:
> SELECT assignmentleader, COUNT(*) AS numof
> FROM Assignments
> GROUP BY assignmentleader
> HAVING COUNT(*)>= ALL
> (SELECT COUNT(*)
> FROM Assignments
> GROUP BY assignmentleader);
>
Thanks dude (and Aaron), you guys rock.

> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>