CODEWITHSUNDEEP
sqlsql-serverms-access
delnav222
delnav222

Reputation: 19

Get count on multiple columns from one table in one query using SQL?

This example shows what works, however, there are six other positions besides System Operator in the same table and I need to count how many times this 'Person' is displayed in the other positions as well.

One record's 'Person' can be a System Operator and in another the 'Person' can be the Engineer. So I want a query that would be able to count how many times this 'Person' was an engineer or system operator in the same query.

When I add a Count() function for Engineer it only attempts to return values in which the person is both an Engineer and System Operator and I'm looking to count how many times this person was either distinctly.

Sample Data would include the following:

Ultimately, I need to generate a report or have a form that has a combo box with a list of people (Bob, Joe, Gregg) or all employees that when I select an individual it will tell me how many times that person was in each capacity. The code below returns the number 4. 

SELECT Count(tblEventPersonnel.[System Operator]) AS [CountOfSystem Operator]
FROM tblEventPersonnel
GROUP BY tblEventPersonnel.[System Operator]
HAVING (((tblEventPersonnel.[System Operator]) Like "Person"));

Upvotes: 0

Views: 1604

Answers (4)

delnav222
delnav222

Reputation: 19

This code worked!

SELECT Sum(IIf(tblEventPersonnel.[System Operator]="Person",1,0)) AS [CountOfSystem Operator],
       Sum(IIf(tblEventPersonnel.[Test Engineer 1]="Person",1,0)) AS [CountOfTest Engineer 1]
FROM tblEventPersonnel;

Upvotes: 0

Racil Hilan
Racil Hilan

Reputation: 25371

You can use the SUM function instead of the COUNT function if your [System Operator] (and the other fields) use the value 1 to indicate that the person is a system operator and 0 to indicate that the person is not:

SELECT SUM(tblEventPersonnel.[System Operator]) AS [CountOfSystem Operator],
       SUM(tblEventPersonnel.[Engineer One]) AS [CountOfEngineerOne]
FROM tblEventPersonnel

However, in your case those fields store the person. You can use CASE WHEN inside the SUM function to make the counting conditional. If the table is normalized and the ID of the person is stored in those fields, you can use a simple > 0 as condition. But your table design doesn't seem to be normalized, because you're storing the name of the person instead of the ID. In this case you need to use IS NOT NULL as condition:

SELECT SUM(CASE WHEN tblEventPersonnel.[System Operator] IS NOT NULL THEN 1 END) AS [CountOfSystem Operator],
       SUM(CASE WHEN tblEventPersonnel.[Engineer One] IS NOT NULL THEN 1 END) AS [CountOfEngineerOne]
FROM tblEventPersonnel
WHERE tblEventPersonnel.[System Operator] = "Bob"
   OR tblEventPersonnel.[Engineer One] = "Bob";

MS Access doesn't support CASE WHEN, so if you're using MS Access, then use the IIF() function instead:

SELECT SUM(IIF(tblEventPersonnel.[System Operator] IS NOT NULL, 1, NULL)) AS [CountOfSystem Operator],
       SUM(IIF(tblEventPersonnel.[Engineer One] IS NOT, 1, NULL)) AS [CountOfEngineerOne]
FROM tblEventPersonnel
WHERE tblEventPersonnel.[System Operator] = "Bob"
   OR tblEventPersonnel.[Engineer One] = "Bob";

Upvotes: 1

dsmeclipse
dsmeclipse

Reputation: 36

I think the way the table is designed is not friendly for your current use. For me, the data would be easier to visualize and query again using a pivot. Here's a great article on how to effectively use pivot in sql server:

https://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query

Here's another discussion about this on stackoverflow:

Convert Rows to columns using 'Pivot' in SQL Server

Since you have this tagged as MS Access and SQL Server, here's another user's experience with pivot in MS ACCESS:

Pivoting data in MS Access

Upvotes: 0

Jeffrey
Jeffrey

Reputation: 538

You could use a sub query union

SELECT Count(CountofPerson) AS PositionCount FROM (SELECT Count(tblEventPersonnel.[System Operator]) AS [CountOfPerson] FROM tblEventPersonnel GROUP BY tblEventPersonnel.[System Operator] HAVING (tblEventPersonnel.[System Operator] Like "Person") UNION SELECT Count(tblEventPersonnel.[Engineer One]) AS [CountOfPerson] FROM tblEventPersonnel GROUP BY tblEventPersonnel.[Engineer One] HAVING (tblEventPersonnel.[Engineer One] Like "Person") );

Add other fields as needed.

Upvotes: 0

Related Questions