NickName:Tauseef Hussain Ask DateTime:2015-07-01T00:28:25

SQL: GROUP BY Clause

SELECT
    (1.0*( SELECT SUM(r.SalesVolume)
           FROM
               RawData r
               INNER JOIN Product p
                   ON r.ProductId = p.ProductId
           WHERE p.Distributor in ('TF1','WARNER')
           GROUP BY p.Distributor
         )
         /
         ( SELECT SUM(r.SalesVolume)
           FROM RawData r
         )*100)
    ;

The query above leads to an error:

Lookup Error - SQL Server Database Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Is the issue with the GROUP BY Clause? Not sure how to go about this.

Copyright Notice：Content Author:「Tauseef Hussain」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/31143589/sql-group-by-clause

Answers

Jeffrey Wieder 2015-06-30T16:32:21

This part of your query is the problem:\n\nSELECT SUM(r.SalesVolume) FROM RawData r\nINNER JOIN Product p\nON r.ProductId = p.ProductId\nWHERE p.Distributor in ('TF1','WARNER') GROUP BY p.Distributor\n ^^^^^^^^^^^^^^^^^^^^^^\n\n\nYou are Grouping BY p.Distributor and there could possibly be two of those based on your where clause. WHERE p.Distributor in ('TF1','WARNER'). You can take the group by out and it will have a total SUM of both TF1 and WARNER\n\nSELECT\n(1.0 * (SELECT SUM(r.SalesVolume) FROM RawData r\n INNER JOIN Product p ON r.ProductId = p.ProductId\n WHERE p.Distributor in ('TF1','WARNER')\n) / (SELECT SUM(r.SalesVolume) FROM RawData r)*100);\n\n\nIf you are meaning to get a percentage of sales volume by distributor you should use this instead.\n\nSELECT p.Distributor, SUM(r.SalesVolume) as Sales, ROUND((SUM(r.SalesVolume) / (SELECT SUM(r.SalesVolume) FROM RawData r) * 100), 1) as percentOfTotal\nFROM RawData r\nINNER JOIN Product p ON r.ProductId = p.ProductId\nWHERE p.Distributor in ('TF1','WARNER')\nGROUP BY p.Distributor\n\n\nI suggest using the ROUND function here. The above query rounds to a single digit after the decimal point. If you want two digits use this:\n\nROUND((SUM(r.SalesVolume) / (SELECT SUM(r.SalesVolume) FROM RawData r) * 100), 2) as percentOfTotal\n",

Rowland Shaw 2015-06-30T16:32:12

Your correlated query:\n\nSELECT SUM(r.SalesVolume) \nFROM RawData r\nINNER JOIN Product p\n ON r.ProductId = p.ProductId\nWHERE p.Distributor in ('TF1','WARNER')\nGROUP BY p.Distributor)\n\n\nWill return a row for each group, i.e. one for TF1, one for WARNER.\n\nYou need to not group by any field that will lead to multiple rows",