PHP SQL Count within select statement-CodePudding

I am trying to make a count select withing a select statement. But u get a error when trying. What am i missing here?

SELECT 
AccountEmail,
AccountPassword,
COUNT(SELECT AccountID FROM Accounts WHERE AccounNotes = 'TEST') AS AccountsCurrently
FROM 
Accounts 
WHERE AccounNotes = 'TEST'ORDER BY `UpdatedLastTime` ASC LIMIT 1

I need the data of only one account and the total amounth of accounts with the AccounNotes "TEST" as AccountsCurrently stored in a variable.

Erro #1064 - Er is iets fout in de gebruikte syntax

CodePudding user response：

SELECT 
AccountEmail,
AccountPassword,
COUNT(*) AS AccountsCurrently
FROM 
Accounts 
WHERE AccounNotes = 'TEST' ORDER BY `UpdatedLastTime` ASC LIMIT 1

CodePudding user response：

Write the COUNT() inside of your parenthetically enclosed sub-SELECT.

SQL: (PHPize Demo)

SELECT AccountEmail,
       AccountPassword,
       (SELECT COUNT(AccountEmail) FROM Accounts WHERE AccounNotes = 'TEST') AccountsCurrently
FROM Accounts
WHERE AccounNotes = 'TEST'
ORDER BY UpdatedLastTime DESC
LIMIT 1

I need the latest updated row in my result.

Then DESC should be used, not ASC.