counting all records in table and putting it into a variable inside mysql itself

Question

I have a table named financial_trans which consist of id (AI,INT) amount (DOUBLE) acadYear (VARCHAR) // probably '2020-2021'

now I want to count the similar acadYear using group by and store it into a mysql variable and my query for that is

SELECT s.total as total 
INTO @year 
FROM (
    SELECT COUNT(acadYear) as total 
    FROM  financial_trans 
    GROUP BY acadYear
) s

but throws an error like below,

#1172 - Result consisted of more than one row

What are you expecting by doing COUNT(acadYear) as `acadYear` is of type VARCHAR in your question? — njari, Jul 26 '21 at 18:49
Please provide sample data and desired results. It sounds like you want to store a table into a variable, and that is not possible. — Gordon Linoff, Jul 26 '21 at 19:02
I want to get the count and use it in the loop for further results...my main result is to find top 5 amount from the table for every acadYear. @FaNo_FN — suraj kumar, Jul 27 '21 at 07:23

rkosegi · Answer 1 · 2021-07-26T18:45:53.907

Your sub-statement SELECT COUNT(acadYear) as total FROM financial_trans GROUP BY acadYear yields more then 1 row, so it can fit into variable @year.

That basically means that you have records for more then 1 distinct year in your financial_trans table. If you need to count records only for specific year, just add WHERE acadYear = XXXX into subquery. Or even better, SELECT COUNT(1) FROM financial_trans WHERE acadYear = XXXX.

Side note: DOUBLE is not ideal column type for financial records, you should use DECIMAL

counting all records in table and putting it into a variable inside mysql itself

1 Answers1