my code:

SELECT ROW_NUMBER() OVER(ORDER BY Title ASC) as row, 
       Title, 
       BookNo, 
       COUNT(*) as Copies, 
       COUNT(Status = 1) 
from tbbook 
GROUP BY Title

result i got

CodePudding user response：

1. The query is incorrect, your GROUP BY expression is partial. Read about ONLY_FULL_GROUP_BY SQL Mode, you'd fix the query.
2. COUNT(Status = 1) shows the amount of rows where Status is not NULL. Use SUM(Status = 1).

Check this:

SELECT ROW_NUMBER() OVER(ORDER BY Title ASC) as RowNumber, 
       Title, 
       BookNo, 
       COUNT(*) AS CopiesAmount, 
       SUM(Status = 1) CopiesWithStatus_1
from tbbook 
GROUP BY Title, BookNo
ORDER BY Title ASC

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=024afcc763715d6ffb52fee97156a797

CodePudding user response：

I found the answer and its subquery inner join

my code:

SELECT ROW_NUMBER() OVER(ORDER BY Title ASC), tbbook.Title, COUNT(*), COALESCE(Available, 0) as Available
FROM tbbook 
LEFT JOIN (
    SELECT Title, COUNT(*) as Available
    from tbbook
    WHERE Status = 1
    GROUP by Title
    ) AS n on tbbook.Title = n.Title
GROUP BY Title