Can I do this with a SQL staement?

Posted on 2011-05-03
Last Modified: 2012-08-13
I have a huge MySQL database that I keep track of worker assignments that have been assigned by supervisors. I have some PHP forms the supervisors assign things to workers via a browser. My question is, is there a way for me to use a SQL query to look at the MySQL database & count & display the number of assignments that is assigned to each worker? For instance, the database name is DATABASE1 & the table is named ASSIGNMENTS. In the ASSIGNMENTS table I have a field named WORKERS. The workers are identified in this field by worker1, worker2, worker3.....I would like to have a SQL that will look at the WORKERS field name & count how many times it sees worker1 & display it in a browser. I assume I would use PHP code to do this??? It would look like something below:

[b]Worker Name[/b]  [b]Assigned Work[/b]
Worker1               5
Worker2               3
Worker3               2


Thanks for any help provided.
Question by:wantabe2
    LVL 32

    Expert Comment


    select WORKERS, Count(*) as [Assigned Work]
    group by WORKERS
    LVL 107

    Expert Comment

    by:Ray Paseur
    LVL 12

    Expert Comment

    by:Mohamed Abowarda
    LVL 15

    Author Comment

    okay, I'm still having issues with this....I have my php page like I need it in to display but still having problems with the SQL...

    I've looked at the example above but just can't get a grasp around brain is overloaded today.

    To make it more simple for me I would like to just have a SQL that counts the number of assignments for worker1 & displays it in a browser like below:

    [b]Worker Name   Assignments[/b]
    Worker1                 24

    How can I get the SQL to just count each time it see's WORKER1 name listed in the worker field? This would be one way to get what I need.
    	   <table border='5'>
    <th>Worker Name</th>
    $query="SELECT worker, assignments COUNT(assignments) FROM psrinfo WHERE location = 'office1' AND worker = 'worker1' ";
    $result = mysql_query($query) or die(mysql_error());  
    while($row = mysql_fetch_array( $result )) {
                <td><?php echo "".$row['worker']; ?></td>
                <td><?php echo "".$row['assignments']; ?></td>
    <?php } ?>            

    Open in new window

    LVL 32

    Accepted Solution


    That will work for one worker, but if you need a list of workers each with assignment count then you need to add the group by clause

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    The viewer will learn how to dynamically set the form action using jQuery.
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    794 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