Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts

    is it possible to return total rows and number of unique departments in one query?

    Hi All,

    i have the following query
    Code:
    SELECT *
                FROM tbl_jobs AS job
                LEFT JOIN tbl_job_department AS jdept ON job.jobID = jdept.jobID
                LEFT JOIN tbl_job_location AS loc ON job.jobID = loc.jobID
                LEFT JOIN tbl_places AS place ON loc.placeID = place.placeID
                LEFT JOIN tbl_departments as dept ON jdept.deptID = dept.deptID 
                WHERE place.placeID = $placeID
    and can easily get the total records by using mysqli_num_rows on the above query, but i also want to get a total count of the unique deptID's within the same query.

    so i can return something like "12 jobs found from 4 departments"

    can anyone help please?
    thanks

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    ok i have tried using the count distinct function within mysql like so
    Code:
    SELECT * , COUNT( DISTINCT dept.deptID ) AS count
    FROM tbl_jobs AS job
    LEFT JOIN tbl_job_department AS jdept ON job.jobID = jdept.jobID
    LEFT JOIN tbl_job_location AS loc ON job.jobID = loc.jobID
    LEFT JOIN tbl_places AS place ON loc.placeID = place.placeID
    LEFT JOIN tbl_departments AS dept ON jdept.deptID = dept.deptID
    WHERE place.placeID =993
    LIMIT 0 , 30
    and it gives me the correct number of departments "i think" but its only returning 1 row of results and i know there are at least two records this query should pull out

    any ideas please?

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    seems as tho i was over thinking this problem
    PHP Code:
                $sql "SELECT count( * ) as records , COUNT( DISTINCT dept.deptID ) AS departments
                FROM tbl_jobs AS job
                LEFT JOIN tbl_job_department AS jdept ON job.jobID = jdept.jobID
                LEFT JOIN tbl_job_location AS loc ON job.jobID = loc.jobID
                LEFT JOIN tbl_places AS place ON loc.placeID = place.placeID
                LEFT JOIN tbl_departments AS dept ON jdept.deptID = dept.deptID
                WHERE place.placeID = $placeID"
    ;
                
    $get_job mysqli_query($con,$sql)or die(mysqli_error($con));
                
    $jobs $row['records'];
                
    $departments $row['departments']; 
    seems to return the output required


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •