Subquery returned more than 1 value This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

Question

I run the following query:

SELECT Date,
       Branch,
       Total,
       (SELECT CAST(AVG(Total) AS INT) 
          FROM MaySales 
         GROUP BY Branch) AS AvgSales
  FROM MaySales

The result I'm looking for is to display the monthly avg beside daily sales so I can do case statement .

date	branch	day total	monthly avg	case statement
05\05	a	4500	5000	below avg
05\06	a	5000	5000	avg
05\05	b	6000	4500	above avg

Please provide sample data and desired results and formatted text (not images). The error is very clear, your sub-query is returning more than one result. — Dale K, Jul 10 '22 at 06:49
In that case you need to correlate your sub-query to your main query. And you shouldn't need to cast anything. — Dale K, Jul 10 '22 at 07:11
Using a `group by` automatically assumes multiple rows. This shouldn't be a shock. — shawnt00, Jul 10 '22 at 07:14

score 1 · Accepted Answer · edited Jul 10 '22 at 08:54

1

The current query tries to return multiple rows for an individual column, while the other columns return once per each row naturally. So it fails to execute. Perhaps using a correlated subquery would be a solution, but would not be an ideal one. Rather prefer using a window function such as:

SELECT Date, Branch, Total,
    AVG(Total) OVER (PARTITION BY Branch) AS AvgSales
FROM MaySales

edited Jul 10 '22 at 08:54

Dale K

25,246
15
42
71

answered Jul 10 '22 at 07:13

Barbaros Özhan

59,113
10
31
55

JayD · Answer 2 · 2022-07-10T07:24:34.000

-1

I can't comment within original question but it looks like your selecting all the rows from your MaySales table, which I'm assuming is for the month of May. In your subquery, and given your intention is to group by month, you have no month operator.

   SELECT year(date) as BranchYear, month(date) as BranchMonth, branch, CAST(avg(TOTAL) AS int)
     FROM MaySales
     GROUP BY year(date), month(date), Branch

Try something like the above to get your data down to a monthly granularity. Once you understand this, you'll be in a much better position to format your data on the assumption that you want to display your normal daily totals with your monthly total included.

Further to the above, I would move away from just having a 'MaySales' table as this would imply that you would have a table for every month of the year. What would happen then if data would extend for years? Have a 'BranchSales' table with DATE, branch, TOTAL columns which I'd assume are for daily totals and then you are in a position to calculate average totals for any time period you wish greater than a day including weekly, monthly, quarterly and yearly totals

edited Jul 10 '22 at 07:24

answered Jul 10 '22 at 07:14

JayD

69
4

1

Syntax error, you might want to remove `) AS avgsales` – Dale K Jul 10 '22 at 07:15
And in fact it appears OP wants to keep day totals, not just give a monthly summary. – Dale K Jul 10 '22 at 07:17
Further to the above, I would move away from just having a 'MaySales' table as this would imply that you would have a table for every month of the year. What would happen then if data would extend for years? Have a 'BranchSales' table with DATE, branch, TOTAL columns which I'd assume are for daily totals and then you are in a position to calculate average totals for any time period you wish greater than a day including weekly, monthly, quarterly and yearly totals. – JayD Jul 10 '22 at 07:21
1

Indeed, you should add that to your answer. – Dale K Jul 10 '22 at 07:22

Subquery returned more than 1 value This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

2 Answers2