CODEWITHSUNDEEP
mysql
Cloud Strifez
Cloud Strifez

Reputation: 61

MySQL getting and Invalid use of group function

I'm trying to count the result of tc.personnel_id if there's more than 1 result, then the output is 1. And then finally, it will get the sum. But I am getting an "Invalid use of group function" error when i execute this code. Any help will be appreciated. 

SELECT  tv.id,
        tv.vesselName,
        SUM(IF(tpi.returningCrew = 0, 1, 
                IF(
                    COUNT(tc.personnel_id) > 1, 1, 0)
                )
            ) AS ex_crew,
        SUM(IF(tpi.returningCrew = 1, 1, 0)) AS new_hire
FROM tbl_vessels AS tv
LEFT JOIN tbl_personnel_info AS tpi
ON tv.id = tpi.lastJoinedVsl
LEFT JOIN tbl_contracts AS tc
ON tpi.id = tc.personnel_id
LEFT JOIN tbl_management AS tm
ON tm.id = tv.management
WHERE tpi.emp_status = 'ON-BOARD'
AND tc.status = 'ACTIVE'
AND tv.trade_route = 'WORLD WIDE'
AND tm.management = 'TOKYO'
GROUP BY vesselName;

Upvotes: 0

Views: 34

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31832

Aggregate functions can not be nested. So something like SUM(.. COUNT(..) ..) is not possible. You might try something like this:

SELECT  tv.id,
        tv.vesselName,
        CASE WHEN COUNT(tc.personnel_id) > 1
            THEN SUM(IF(tpi.returningCrew = 0, 1, 1)
            ELSE SUM(IF(tpi.returningCrew = 0, 1, 0)
        END AS ex_crew,
        SUM(IF(tpi.returningCrew = 1, 1, 0)) AS new_hire
FROM [..]

SUM(IF(tpi.returningCrew = 0, 1, 1) can be rewriten to SUM(1) or to COUNT(*).

SUM(IF(tpi.returningCrew = 0, 1, 0) cann also be rewritten to SUM(tpi.returningCrew = 0)

And SUM(IF(tpi.returningCrew = 1, 1, 0)) to SUM(tpi.returningCrew = 1)

So the final select could be:

SELECT  tv.id,
        tv.vesselName,
        CASE WHEN COUNT(tc.personnel_id) > 1
            THEN COUNT(*)
            ELSE SUM(tpi.returningCrew = 0)
        END AS ex_crew,
        SUM(tpi.returningCrew = 1) AS new_hire
FROM [..]

Upvotes: 1

Related Questions