September 17, 2007 at 10:49 am
I have a table which I am trying to count and group, but the primary is preventing me from retrieving the results Listed Below is my table. How can I get the results for each employeenominationID with using the primary key ReasonID
CREATE
TABLE [dbo].[Reason](
[ReasonID] [int]
IDENTITY(1,1) NOT NULL,
[Employeenominationid] [int]
NULL,
[Reason] [varchar]
(6000) NOT NULL,
[DateCreated] [datetime]
NOT NULL CONSTRAINT [DF_Reason_datecreated] DEFAULT (getdate()),
[NominatedByFirstName] [varchar]
(50) NULL,
[NominatedByLastName] [varchar]
(50) NULL,
[NomineeFirstName] [varchar]
(50) NULL,
[NomineeLastName] [varchar]
(50) NULL,
[EmployeeSelected] [smallint]
NULL,
CONSTRAINT [PK_Reason] PRIMARY KEY CLUSTERED
(
send table
CREATE
TABLE [dbo].[EmployeeNominations](
[EmployeeNominationid] [int]
IDENTITY(1,1) NOT NULL,
[NominatedByFirstName] [varchar]
(50) NOT NULL,
[NominatedByLastName] [varchar]
(50) NOT NULL,
[NomineeFirstName] [varchar]
(50) NOT NULL,
[NomineeLastName] [varchar]
(50) NOT NULL,
[NominatedByName] [varchar]
(50) NULL,
[NomineeName] [varchar]
(50) NULL,
[NomineeDept] [varchar]
(50) NOT NULL,
[ReasonForNominating] [text]
NULL,
[EmployeeSelected] [smallint]
NOT NULL CONSTRAINT [DF_EmployeeNominations_EmployeeSelected] DEFAULT (0),
[DateCreated] [datetime]
NOT NULL CONSTRAINT [DF_EmployeeNominations_DateCreated] DEFAULT (getdate()),
[DateModified] [datetime]
NOT NULL CONSTRAINT [DF_EmployeeNominations_DateModified] DEFAULT (getdate()),
CONSTRAINT [pk_nominationid] PRIMARY KEY CLUSTERED
(
My count statement looks like this:
SELECT
R.NomineeFirstName, R.NomineeLastName, E.NomineeDept, COUNT(E.EmployeeNominationid) AS NominationsCount
FROM
dbo.Reason AS R INNER JOIN
dbo
.EmployeeNominations AS E ON R.Employeenominationid = E.EmployeeNominationid
GROUP
BY R.NomineeFirstName, R.NomineeLastName, E.NomineeDept
But I am trying to use all the columns in the reason tble, please take notice of the data type.
September 17, 2007 at 12:55 pm
September 17, 2007 at 1:18 pm
NomineeFirstName NomineeLastName NomineeDept NominationsCount
AmanPreet Singh is 1
Dyana Pearson is 1
Jaime Maccou is 3
matt Chambers is 1
Paul Nelson is 2
Rukmini Yalamanchi is 2
Shannon Robison is 1
The Data results I will lke looks like this based on the select statement I posting but when
adding the primary key is looks like this
NomineeFirstName NomineeLastName NomineeDept ReasonID NominationsCount
Jaime Maccou is 1 1
Paul Nelson is 2 1
Dyana Pearson is 3 1
matt Chambers is 4 1
Shannon R obison is 5 1
Rukmini Yalamanchi is 6 1
Jaime Maccou is 7 1
Rukmini Yalamanchi is 8 1
Jaime Maccou is 9 1
Paul Nelson is 10 1
AmanPreet Singh is 11 1
September 17, 2007 at 2:27 pm
It's a bit unclear from your original post, but which columns are you adding the primary key on?
September 17, 2007 at 2:55 pm
Jaime -
are you trying to return the count next to EACH ID, or are you looking for any one of the ID?
If door #1 you'd want something like:
select r_outer.reasonID, temp.NomineeFirstName,temp.NomineeLastName, temp.NomineeDept,temp.NominationsCount
from reason r_outer inner join
(SELECT R.NomineeFirstName, R.NomineeLastName , E.NomineeDept, COUNT(E.EmployeeNominationid) AS NominationsCount
FROM
dbo.Reason AS R INNER JOIN
dbo
.EmployeeNominations AS E ON R.Employeenominationid = E.EmployeeNominationid
GROUP
BY R.NomineeFirstName, R.NomineeLastName, E.NomineeDept) temp on temp.NomineeFirstName=r_outer.NomineeFirstName and temp.NomineelastName=r_OUTER.NomineelastName
----------------------------------------------------------------------------------
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?
September 17, 2007 at 3:19 pm
The Primary key is coming from the reason table.
September 17, 2007 at 3:29 pm
Yes, it looks like you create a PK on both tables, but what I want to know is on which columns? Look back at your original post. It shows your PK definition, but the column names are missing.
September 17, 2007 at 3:34 pm
Thanks for helping out
I will like to do both, the problem is since the reasonID value is unique, the NominationCount will be duplicated for each reasonID because as you see I am trying to count the EmployeeNiminationID and for each EmployeeNominationID value that is counted it will be counted for each reasonID. I hope I have explain or clarified it.
This is what your result set looks like
reasonID NomineeFirstName NomineeLastName NomineeDept NominationsCount
1 Jaime Maccou is 3
2 Paul Nelson is 2
3 Dyana Pearson is 1
4 matt Chambers is 1
5 Shannon Robison is 1
6 Rukmini Yalamanchi is 2
7 Jaime Maccou is 3
8 Rukmini Yalamanchi is 2
9 Jaime Maccou is 3
10 Paul Nelson is 2
11 AmanPreet Singh is 1
September 17, 2007 at 3:41 pm
here are the two columns
[ReasonID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Reason] PRIMARY KEY CLUSTERED - from reason table
CONSTRAINT [pk_nominationid] PRIMARY KEY CLUSTERED
[EmployeeNominationid] [int] IDENTITY(1,1) NOT NULL- From EmployeeNominations
September 17, 2007 at 3:51 pm
OK, adding a primary key onto these tables makes no difference to your query the way you have it written. The 2 result sets you show (one before the PK and one after) show a different number of columns. The example you show after adding in the PK has the ReasonID added into it. This would change the group by as well as the COUNT. This example, using your tables and test data, shows that your original query produces the results that you say you want. Am I missing something here?
DROP TABLE #Reason
CREATE TABLE #Reason (
[ReasonID] [int] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Reason] PRIMARY KEY CLUSTERED ,
[Employeenominationid] [int] NULL,
[Reason] [varchar](6000) NOT NULL,
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_Reason_datecreated] DEFAULT (getdate()),
[NominatedByFirstName] [varchar](50) NULL,
[NominatedByLastName] [varchar](50) NULL,
[NomineeFirstName] [varchar](50) NULL,
[NomineeLastName] [varchar](50) NULL,
[EmployeeSelected] [smallint] NULL
)
DROP TABLE #EmployeeNominations
CREATE TABLE #EmployeeNominations (
[EmployeeNominationid] [int] IDENTITY(1,1) NOT NULL CONSTRAINT [pk_nominationid] PRIMARY KEY CLUSTERED ,
[NominatedByFirstName] [varchar](50) NOT NULL,
[NominatedByLastName] [varchar](50) NOT NULL,
[NomineeFirstName] [varchar](50) NOT NULL,
[NomineeLastName] [varchar](50) NOT NULL,
[NominatedByName] [varchar](50) NULL,
[NomineeName] [varchar](50) NULL,
[NomineeDept] [varchar](50) NOT NULL,
[ReasonForNominating] [text] NULL,
[EmployeeSelected] [smallint] NOT NULL CONSTRAINT [DF_EmployeeNominations_EmployeeSelected] DEFAULT (0),
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_EmployeeNominations_DateCreated] DEFAULT (getdate()),
[DateModified] [datetime] NOT NULL CONSTRAINT [DF_EmployeeNominations_DateModified] DEFAULT (getdate())
)
INSERT INTO #EmployeeNominations (NominatedByFirstName, NominatedByLastName, NomineeFirstName, NomineeLastName, NomineeDept)
SELECT 'FN1','LN1','AmanPreet','Singh','is' UNION ALL
SELECT 'FN2','LN2','Dyana','Pearson','is' UNION ALL
SELECT 'FN3','LN3','Jaime','Maccou','is' UNION ALL
SELECT 'FN4','LN4','matt','Chambers','is' UNION ALL
SELECT 'FN5','LN5','Paul','Nelson','is' UNION ALL
SELECT 'FN6','LN6','Rukmini','Yalamanchi','is' UNION ALL
SELECT 'FN7','LN7','Shannon','Robison','is' UNION ALL
SELECT 'FN8','LN8','Jaime','Maccou','is' UNION ALL
SELECT 'FN9','LN9','Jaime','Maccou','is' UNION ALL
SELECT 'FN10','LN10','Paul','Nelson','is' UNION ALL
SELECT 'FN11','LN11','Rukmini','Yalamanchi','is'
INSERT INTO #Reason (NomineeFirstName, NomineeLastName, Reason,EmployeeNominationid)
SELECT 'AmanPreet','Singh','',1 UNION ALL
SELECT 'Dyana','Pearson','',2 UNION ALL
SELECT 'Jaime','Maccou','',3 UNION ALL
SELECT 'matt','Chambers','',4 UNION ALL
SELECT 'Paul','Nelson','',5 UNION ALL
SELECT 'Rukmini','Yalamanchi','',6 UNION ALL
SELECT 'Shannon','Robison','',7 UNION ALL
SELECT 'Jaime','Maccou','',8 UNION ALL
SELECT 'Jaime','Maccou','',9 UNION ALL
SELECT 'Paul','Nelson','',10 UNION ALL
SELECT 'Rukmini','Yalamanchi','',11
SELECT R.NomineeFirstName, R.NomineeLastName, E.NomineeDept, COUNT(E.EmployeeNominationid) AS NominationsCount
FROM #Reason AS R
INNER JOIN #EmployeeNominations AS E
ON R.Employeenominationid = E.EmployeeNominationid
GROUP BY R.NomineeFirstName, R.NomineeLastName, E.NomineeDept
September 17, 2007 at 4:06 pm
You can do what you want, but you need to use a different logical concept. If what you have is:
SELECT R.NomineeFirstName, R.NomineeLastName, E.NomineeDept, COUNT(E.EmployeeNominationid) AS NominationsCount
....
And what you need is
SELECT R.NomineeFirstName, R.NomineeLastName, E.NomineeDept, ReasonID, ***COUNT(E.EmployeeNominationid) AS NominationsCount***
Where the COUNT() is the count of all nominations for the combination of R.NomineeFirstName, R.NomineeLastName, and E.NomineeDept, then you have two recordsets that need to be joined:
SELECT R.NomineeFirstName,
R.NomineeLastName,
E.NomineeDept,
ReasonID,
CountSource.NominationsCount
FROM dbo.Reason AS R
INNER JOIN dbo.EmployeeNominations AS E
ON R.Employeenominationid = E.EmployeeNominationid
INNER JOIN (
SELECT R.NomineeFirstName, R.NomineeLastName, E.NomineeDept, COUNT(E.EmployeeNominationid) AS NominationsCount
FROM dbo.Reason AS R
INNER JOIN dbo.EmployeeNominations AS E
ON R.Employeenominationid = E.EmployeeNominationid
GROUP BY R.NomineeFirstName, R.NomineeLastName, E.NomineeDept
) As CountSource
On CountSource.NomineeFirstName = R.NomineeFirstName
AND CountSource.NomineeLastName = R.NomineeLastName
AND CountSource.NomineeDept = E.NomineeDept
P.S. I hate the tab reformatting here.
September 17, 2007 at 5:00 pm
yes, the results I first post give me that result set. But I wanted to know if there was a way of adding the Primary Key(ReasonID) with out affecting the count.
I appreciated the time and effort you have taken.
September 18, 2007 at 9:46 am
Building on the example data I posted earlier, this should work for you. Keep in mind that this type of JOIN assumes that there will not be any duplicate Nominee firstname/lastname combinations. If you think there may be, add in an additional join back to your employee nominations table and join by department also. This will then assume that the nominee names will not be duplicated within a department.
SELECT R.NomineeFirstName, R.NomineeLastName, t.NomineeDept, R.ReasonID, NominationsCount
FROM #Reason R
INNER JOIN (
SELECT R.NomineeFirstName, R.NomineeLastName, E.NomineeDept, COUNT(E.EmployeeNominationid) AS NominationsCount
FROM #Reason AS R
INNER JOIN #EmployeeNominations AS E
ON R.Employeenominationid = E.EmployeeNominationid
GROUP BY R.NomineeFirstName, R.NomineeLastName, E.NomineeDept
) t
ON R.NomineeFirstName = t.NomineeFirstName AND R.NomineeLastName = t.NomineeLastName
September 18, 2007 at 5:49 pm
Nice to see it when folks post both example data and code... nicely done, John.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply