Solved

sql count if

Posted on 2014-04-09
6
279 Views
Last Modified: 2014-04-15
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
Comment
Question by:sharris_glascol
  • 3
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39988988
SELECT COUNT(DISTINCT empid)
FROM your_table
WHERE odd_no = 123456 AND oper_no = 10

Open in new window

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

Open in new window

0
 

Author Comment

by:sharris_glascol
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 65

Expert Comment

by:Jim Horn
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

Open in new window

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

Open in new window

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:sharris_glascol
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 65

Expert Comment

by:Jim Horn
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

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 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

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now