Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# sql count if

Posted on 2014-04-09
Medium Priority
304 Views
I am needing to look at a table and see if columns have a match.  I want to see if multiple employees have been clocked onto the same work order and operation.  I have the columns odd_no, Oper_no and empid.  for example I may have the work order number 123456 with operation number 10.  I want to COUNT how many different employees worked on that operation..
0
Question by:sharris_glascol
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 66

Expert Comment

ID: 39988988
``````SELECT COUNT(DISTINCT empid)
FROM your_table
WHERE odd_no = 123456 AND oper_no = 10
``````
You'll want to look at the full data set just to make sure the above is correct, and any duplicate values are being handled

``````SELECT odd_no, oper_no, emp_id
FROM your_table
WHERE odd_no = 123456 AND oper_no = 10
ORDER BY odd_no, oper_no, emp_id
``````
0

Author Comment

ID: 39988993
what if I want to look at all work orders and operations to see how many employees where clocked into the same one and not just one  exact work order?
0

LVL 66

Expert Comment

ID: 39989017
Not entirely sure what you mean by 'the same one', but give this a whirl..

List...

``````SELECT odd_no, oper_no, emp_id
FROM your_table
ORDER BY odd_no, oper_no, emp_id
``````
Count...

``````SELECT odd_no, oper_no, COUNT(emp_id) as empl_id_count
FROM your_table
GROUP BY odd_no, oper_no
ORDER BY odd_no, oper_no
``````
0

Author Comment

ID: 39989044
Here is what i need to count

work order     operation number     employee number     count
12345                  10                                2321                           2
2468                      20                               1234                          2
12345                     10                               1234                         2
2468                       20                                2321                        2
13579                    10                                 2321                        1

This is kinda what I am looking for....
0

LVL 66

Expert Comment

ID: 39989172
Nice mockup data.  Next time please add that to your original question, so we don't have to spend time flushing out requirements.

Based on the set above, give this a whirl, and add an ORDER BY clause to sort it by whatever is most readable..
``````SELECT
odd_no as work_order,
oper_no as operation_number,
emp_id as employee_number,
COUNT(emp_id) as empl_id_count
FROM your_table
GROUP BY odd_no, oper_no, emp_id
``````
0

LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 39990666
Both sample data and expected results are best practice when asking this type of question. At this point I think we have the expected result but can deduce the sample data.

Jim is ever so close but I believe you need

COUNT() OVER()

to match that expected result. Like this:
``````    CREATE TABLE Your_Table
([odd_no] int, [Oper_no] int, [empid] int)
;

INSERT INTO Your_Table
([odd_no], [Oper_no], [empid])
VALUES
(12345, 10, 1234),
(12345, 10, 2321),
(13579, 10, 2321),
(2468, 20, 2321),
(2468, 20, 1234)
;

**Query 1**:

SELECT
odd_no                                           AS work_order
, oper_no                                          AS operation_number
, empid                                            AS employee_number
, COUNT(empid) OVER (PARTITION BY odd_no, oper_no) AS empl_id_count
FROM your_table
ORDER BY
odd_no,
oper_no,
empid

**[Results][2]**:

| WORK_ORDER | OPERATION_NUMBER | EMPLOYEE_NUMBER | EMPL_ID_COUNT |
|------------|------------------|-----------------|---------------|
|       2468 |               20 |            1234 |             2 |
|       2468 |               20 |            2321 |             2 |
|      12345 |               10 |            1234 |             2 |
|      12345 |               10 |            2321 |             2 |
|      13579 |               10 |            2321 |             1 |

http://sqlfiddle.com/#!3/4002c/1
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
###### Suggested Courses
Course of the Month9 days, 21 hours left to enroll