Basic COUNT query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Basic COUNT query

Author

Topic

jrobin747
Starting Member

48 Posts

Posted - 2013-08-07 : 11:37:37

I am trouble getting the count of applications. In the below query MerApp.ApplicationID represents the applications. I'm trying to get a results of a count of applications for each sales team member.

Before adding the COUNT() function. My query results show 3811 because it shows the sales team member numerous times because he has many applications with different IDs. For example sales team member A shows up in 75 rows because he has 75 different applications assigned to him.

SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE MerApp.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus

I want sales team member A to show up once with the count of applications to be 75 because that's how many are assigned to him.

This didn't work for me:
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, COUNT (MerApp.ApplicationID) AS [Applications], stm.UserID, MerApp.ActiveStatus
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE MerApp.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus

It didn't count the number of applications for each person.

I have about 14 different sales team members. So I'm trying to have a query that produces 14 rows not 3811 rows

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-07 : 12:08:48

Remove MerApp.ApplicationID from the GROUP BY.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

jrobin747
Starting Member

48 Posts

Posted - 2013-08-07 : 12:10:19

that did it but I don't understand why

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-07 : 13:04:33

If you are aggregating the ApplicationID, you cannot also group by it. Then you will include ApplicationID as aggregate measure and get the count for each unique ApplicationID too.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-07 : 13:11:28

quote:
Originally posted by jrobin747

that did it but I don't understand why

thanks

Consider the following simple example:
Query 1 returns how many Cats and Dogs there.

Query 2 returns how many dogs and cats with distinct names are there

[CODE]
DECLARE @TABLE1 TABLE(PET VARCHAR(10), Name VARCHAR(10));

INSERT INTO @TABLE1 VALUES
('Cat', 'Chester'),
('Cat', 'Pumpkin'),
('Cat', 'Newton'),
('Dog', 'Roth'),
('Dog', 'Spark'),
('Dog', 'Spark'),
('Dog', 'Buster'),
('Dog', 'Arrow');

-- Query1
SELECT Pet, Count(Name) from @Table1 GROUP BY Pet;

-- Query2
SELECT Pet, Name, Count(Name) from @Table1 GROUP BY Pet, Name;
[/CODE]

jrobin747
Starting Member

48 Posts

Posted - 2013-08-07 : 13:11:55

Makes sense as to why I was getting a count of 1 for each application before I removed it out of the group by section

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources