Use of HAVING without GROUP BY not working as expected












1














I am starting to learn SQL Server, in the documentation found in msdn states like this




HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.




This made me to think that we can use having without a groupBy clause, but when I am trying to make a query I am not able to use it.



I have a table like this



CREATE TABLE [dbo].[_abc]
(
[wage] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[_abc] (wage)
VALUES (4), (8), (15), (30), (50)
GO


Now when I run this query, I get an error



select * 
from [dbo].[_abc]
having sum(wage) > 5


Error:



enter image description here










share|improve this question




















  • 1




    Try: select sum(wage), count(1) from [dbo].[_abc] having sum(wage)>5
    – JohnLBevan
    Nov 10 '18 at 8:38






  • 1




    What exactly would you want that query to return? Can you add an example of that expected output?
    – johey
    Nov 10 '18 at 9:00










  • @johey, i just want to learn the implementation of having without the groupBy Clause, i used this table becasue it is simple and would be easy to understand
    – Lijin Durairaj
    Nov 10 '18 at 9:04










  • As already stated by John and Pratap, when using group by and/or having, you cannot just use anything you want in the select list. According to msdn (the statement you copied), without a group by the result will be put into an "implicit single, aggregated group". So, using * in your select (or explicitly using the column wage) will not work as these different values cannot be converted to just one group (one resulting record).
    – johey
    Nov 10 '18 at 9:05






  • 1




    Yes, with select wage you are asking to select all wages (so multiple records needs to be returned), with select sum(wage) you will only get one result (the sum of all wages). The "implicit single, aggregate group" does not allow multiple results to be returned, hence the error you are getting.
    – johey
    Nov 10 '18 at 10:57
















1














I am starting to learn SQL Server, in the documentation found in msdn states like this




HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.




This made me to think that we can use having without a groupBy clause, but when I am trying to make a query I am not able to use it.



I have a table like this



CREATE TABLE [dbo].[_abc]
(
[wage] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[_abc] (wage)
VALUES (4), (8), (15), (30), (50)
GO


Now when I run this query, I get an error



select * 
from [dbo].[_abc]
having sum(wage) > 5


Error:



enter image description here










share|improve this question




















  • 1




    Try: select sum(wage), count(1) from [dbo].[_abc] having sum(wage)>5
    – JohnLBevan
    Nov 10 '18 at 8:38






  • 1




    What exactly would you want that query to return? Can you add an example of that expected output?
    – johey
    Nov 10 '18 at 9:00










  • @johey, i just want to learn the implementation of having without the groupBy Clause, i used this table becasue it is simple and would be easy to understand
    – Lijin Durairaj
    Nov 10 '18 at 9:04










  • As already stated by John and Pratap, when using group by and/or having, you cannot just use anything you want in the select list. According to msdn (the statement you copied), without a group by the result will be put into an "implicit single, aggregated group". So, using * in your select (or explicitly using the column wage) will not work as these different values cannot be converted to just one group (one resulting record).
    – johey
    Nov 10 '18 at 9:05






  • 1




    Yes, with select wage you are asking to select all wages (so multiple records needs to be returned), with select sum(wage) you will only get one result (the sum of all wages). The "implicit single, aggregate group" does not allow multiple results to be returned, hence the error you are getting.
    – johey
    Nov 10 '18 at 10:57














1












1








1







I am starting to learn SQL Server, in the documentation found in msdn states like this




HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.




This made me to think that we can use having without a groupBy clause, but when I am trying to make a query I am not able to use it.



I have a table like this



CREATE TABLE [dbo].[_abc]
(
[wage] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[_abc] (wage)
VALUES (4), (8), (15), (30), (50)
GO


Now when I run this query, I get an error



select * 
from [dbo].[_abc]
having sum(wage) > 5


Error:



enter image description here










share|improve this question















I am starting to learn SQL Server, in the documentation found in msdn states like this




HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.




This made me to think that we can use having without a groupBy clause, but when I am trying to make a query I am not able to use it.



I have a table like this



CREATE TABLE [dbo].[_abc]
(
[wage] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[_abc] (wage)
VALUES (4), (8), (15), (30), (50)
GO


Now when I run this query, I get an error



select * 
from [dbo].[_abc]
having sum(wage) > 5


Error:



enter image description here







sql sql-server group-by having






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 14 '18 at 6:55









Salman A

175k66336424




175k66336424










asked Nov 10 '18 at 8:33









Lijin Durairaj

96911733




96911733








  • 1




    Try: select sum(wage), count(1) from [dbo].[_abc] having sum(wage)>5
    – JohnLBevan
    Nov 10 '18 at 8:38






  • 1




    What exactly would you want that query to return? Can you add an example of that expected output?
    – johey
    Nov 10 '18 at 9:00










  • @johey, i just want to learn the implementation of having without the groupBy Clause, i used this table becasue it is simple and would be easy to understand
    – Lijin Durairaj
    Nov 10 '18 at 9:04










  • As already stated by John and Pratap, when using group by and/or having, you cannot just use anything you want in the select list. According to msdn (the statement you copied), without a group by the result will be put into an "implicit single, aggregated group". So, using * in your select (or explicitly using the column wage) will not work as these different values cannot be converted to just one group (one resulting record).
    – johey
    Nov 10 '18 at 9:05






  • 1




    Yes, with select wage you are asking to select all wages (so multiple records needs to be returned), with select sum(wage) you will only get one result (the sum of all wages). The "implicit single, aggregate group" does not allow multiple results to be returned, hence the error you are getting.
    – johey
    Nov 10 '18 at 10:57














  • 1




    Try: select sum(wage), count(1) from [dbo].[_abc] having sum(wage)>5
    – JohnLBevan
    Nov 10 '18 at 8:38






  • 1




    What exactly would you want that query to return? Can you add an example of that expected output?
    – johey
    Nov 10 '18 at 9:00










  • @johey, i just want to learn the implementation of having without the groupBy Clause, i used this table becasue it is simple and would be easy to understand
    – Lijin Durairaj
    Nov 10 '18 at 9:04










  • As already stated by John and Pratap, when using group by and/or having, you cannot just use anything you want in the select list. According to msdn (the statement you copied), without a group by the result will be put into an "implicit single, aggregated group". So, using * in your select (or explicitly using the column wage) will not work as these different values cannot be converted to just one group (one resulting record).
    – johey
    Nov 10 '18 at 9:05






  • 1




    Yes, with select wage you are asking to select all wages (so multiple records needs to be returned), with select sum(wage) you will only get one result (the sum of all wages). The "implicit single, aggregate group" does not allow multiple results to be returned, hence the error you are getting.
    – johey
    Nov 10 '18 at 10:57








1




1




Try: select sum(wage), count(1) from [dbo].[_abc] having sum(wage)>5
– JohnLBevan
Nov 10 '18 at 8:38




Try: select sum(wage), count(1) from [dbo].[_abc] having sum(wage)>5
– JohnLBevan
Nov 10 '18 at 8:38




1




1




What exactly would you want that query to return? Can you add an example of that expected output?
– johey
Nov 10 '18 at 9:00




What exactly would you want that query to return? Can you add an example of that expected output?
– johey
Nov 10 '18 at 9:00












@johey, i just want to learn the implementation of having without the groupBy Clause, i used this table becasue it is simple and would be easy to understand
– Lijin Durairaj
Nov 10 '18 at 9:04




@johey, i just want to learn the implementation of having without the groupBy Clause, i used this table becasue it is simple and would be easy to understand
– Lijin Durairaj
Nov 10 '18 at 9:04












As already stated by John and Pratap, when using group by and/or having, you cannot just use anything you want in the select list. According to msdn (the statement you copied), without a group by the result will be put into an "implicit single, aggregated group". So, using * in your select (or explicitly using the column wage) will not work as these different values cannot be converted to just one group (one resulting record).
– johey
Nov 10 '18 at 9:05




As already stated by John and Pratap, when using group by and/or having, you cannot just use anything you want in the select list. According to msdn (the statement you copied), without a group by the result will be put into an "implicit single, aggregated group". So, using * in your select (or explicitly using the column wage) will not work as these different values cannot be converted to just one group (one resulting record).
– johey
Nov 10 '18 at 9:05




1




1




Yes, with select wage you are asking to select all wages (so multiple records needs to be returned), with select sum(wage) you will only get one result (the sum of all wages). The "implicit single, aggregate group" does not allow multiple results to be returned, hence the error you are getting.
– johey
Nov 10 '18 at 10:57




Yes, with select wage you are asking to select all wages (so multiple records needs to be returned), with select sum(wage) you will only get one result (the sum of all wages). The "implicit single, aggregate group" does not allow multiple results to be returned, hence the error you are getting.
– johey
Nov 10 '18 at 10:57












3 Answers
3






active

oldest

votes


















4














The documentation is correct; i.e. you could run this statement:



select sum(wage) sum_of_all_wages
, count(1) count_of_all_records
from [dbo].[_abc]
having sum(wage) > 5


The reason your statement doesn't work is because of the select *, which means select every columns' value. When there is no group by, all records are aggregated; i.e. you only get 1 record in your result set which has to represent every record. As such, you can only* include values provided by applying aggregate functions to your columns; not the columns themselves.
* of course, you can also provide constants, so select 'x' constant, count(1) cnt from myTable would work.



There aren't many use cases I can think of where you'd want to use having without a group by, but certainly it can be done as shown above.



NB: If you wanted all rows where the wage was greater than 5, you'd use the where clause instead:



select * 
from [dbo].[_abc]
where wage > 5


Equally, if you want the sum of all wages greater than 5 you can do this



select sum(wage) sum_of_wage_over_5 
from [dbo].[_abc]
where wage > 5


Or if you wanted to compare the sum of wages over 5 with those under:



select case when wage > 5 then 1 else 0 end wage_over_five
, sum(wage) sum_of_wage
from [dbo].[_abc]
group by case when wage > 5 then 1 else 0 end


See runnable examples here.





Update based on comments:



Do you need having to use aggregate functions?



No. You can run select sum(wage) from [dbo].[_abc]. When an aggregate function is used without a group by clause, it's as if you're grouping by a constant; i.e. select sum(wage) from [dbo].[_abc] group by 1.



The documentation merely means that whilst normally you'd have a having statement with a group by statement, it's OK to exclude the group by / in such cases the having statement, like the select statement, will treat your query as if you'd specified group by 1



What's the point?



It's hard to think of many good use cases, since you're only getting one row back and the having statement is a filter on that.



One use case could be that you write code to monitor your licenses for some software; if you have less users than per-user-licenses all's good / you don't want to see the result since you don't care. If you have more users you want to know about it. E.g.



declare @totalUserLicenses int = 100
select count(1) NumberOfActiveUsers
, @totalUserLicenses NumberOfLicenses
, count(1) - @totalUserLicenses NumberOfAdditionalLicensesToPurchase
from [dbo].[Users]
where enabled = 1
having count(1) > @totalUserLicenses


Isn't the select irrelevant to the having clause?



Yes and no. Having is a filter on your aggregated data. Select says what columns/information to bring back. As such you have to ask "what would the result look like?" i.e. Given we've had to effectively apply group by 1 to make use of the having statement, how should SQL interpret select *? Since your table only has one column this would translate to select wage; but we have 5 rows, so 5 different values of wage, and only 1 row in the result to show this.



I guess you could say "I want to return all rows if their sum is greater than 5; otherwise I don't want to return any rows". Were that your requirement it could be achieved a variety of ways; one of which would be:



select *
from [dbo].[_abc]
where exists
(
select 1
from [dbo].[_abc]
having sum(wage) > 5
)


However, we have to write the code to meet the requirement, rather than expect the code to understand our intent.



Another way to think about having is as being a where statement applied to a subquery. I.e. your original statement effectively reads:



select wage
from
(
select sum(wage) sum_of_wage
from [dbo].[_abc]
group by 1
) singleRowResult
where sum_of_wage > 5


That won't run because wage is not available to the outer query; only sum_of_wage is returned.






share|improve this answer























  • the reason why i gave this small example is that i want to understand the useage of having without the gruoupBy clasue
    – Lijin Durairaj
    Nov 10 '18 at 8:51










  • my question is how can i use the having clause in the table whichi have designed
    – Lijin Durairaj
    Nov 10 '18 at 8:52










  • i have some quesiton regarding the answer which u have posted "The reason your statement doesn't work is because of the select *" 1.the column in the select clause is irrelevant to the having clause, because all the having clause does is provisions us to use the aggregate function, right? if not please explain it, thanks
    – Lijin Durairaj
    Nov 10 '18 at 8:57






  • 1




    Hopefully my updated answer makes more sense... let me know. As someone learning the language your approach is different to most peoples; you're asking how can I use having without using group by as a way to understand it. In standard use cases you'd have a goal in mind first and your question would be how do I achieve this. As mentioned, it's hard to think of many examples where using having without a group by would make sense; so I'd say make a mental note that it's an option, and move on to learning something else; then in future if you hit a use case, hopefully you'll recall this
    – JohnLBevan
    Nov 10 '18 at 9:14



















1














HAVING without GROUP BY clause is perfectly valid but here is what you need to understand:




  • The result will contain zero or one row


    • The implicit GROUP BY will return exactly one row even if the WHERE condition matched zero rows


    • HAVING will keep or eliminate that single row based on the condition



  • Any column in the SELECT clause needs to be wrapped inside an aggregate function

  • You can also specify an expression as long as it is not functionally dependent on the columns


Which means you can do this:



SELECT SUM(wage)
FROM employees
HAVING SUM(wage) > 100
-- One row containing the sum if the sum is greater than 5
-- Zero rows otherwise


Or even this:



SELECT 1
FROM employees
HAVING SUM(wage) > 100
-- One row containing "1" if the sum is greater than 5
-- Zero rows otherwise


This construct is often used when you're interested in checking if a match for the aggregate was found:



SELECT *
FROM departments
WHERE EXISTS (
SELECT 1
FROM employees
WHERE employees.department = departments.department
HAVING SUM(wage) > 100
)
-- all departments whose employees earn more than 100 in total





share|improve this answer































    0














    In SQL you cannot return aggregate functioned columns directly. You need to group the non aggregate fields



    As shown below example



     USE AdventureWorks2012 ;  
    GO
    SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
    FROM Sales.SalesOrderDetail
    GROUP BY SalesOrderID
    HAVING SUM(LineTotal) > 100000.00
    ORDER BY SalesOrderID ;


    In your case you don't have identity column for your table it should come as below



    Alter _abc
    Add Id_new Int Identity(1, 1)
    Go





    share|improve this answer





















      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53237309%2fuse-of-having-without-group-by-not-working-as-expected%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      4














      The documentation is correct; i.e. you could run this statement:



      select sum(wage) sum_of_all_wages
      , count(1) count_of_all_records
      from [dbo].[_abc]
      having sum(wage) > 5


      The reason your statement doesn't work is because of the select *, which means select every columns' value. When there is no group by, all records are aggregated; i.e. you only get 1 record in your result set which has to represent every record. As such, you can only* include values provided by applying aggregate functions to your columns; not the columns themselves.
      * of course, you can also provide constants, so select 'x' constant, count(1) cnt from myTable would work.



      There aren't many use cases I can think of where you'd want to use having without a group by, but certainly it can be done as shown above.



      NB: If you wanted all rows where the wage was greater than 5, you'd use the where clause instead:



      select * 
      from [dbo].[_abc]
      where wage > 5


      Equally, if you want the sum of all wages greater than 5 you can do this



      select sum(wage) sum_of_wage_over_5 
      from [dbo].[_abc]
      where wage > 5


      Or if you wanted to compare the sum of wages over 5 with those under:



      select case when wage > 5 then 1 else 0 end wage_over_five
      , sum(wage) sum_of_wage
      from [dbo].[_abc]
      group by case when wage > 5 then 1 else 0 end


      See runnable examples here.





      Update based on comments:



      Do you need having to use aggregate functions?



      No. You can run select sum(wage) from [dbo].[_abc]. When an aggregate function is used without a group by clause, it's as if you're grouping by a constant; i.e. select sum(wage) from [dbo].[_abc] group by 1.



      The documentation merely means that whilst normally you'd have a having statement with a group by statement, it's OK to exclude the group by / in such cases the having statement, like the select statement, will treat your query as if you'd specified group by 1



      What's the point?



      It's hard to think of many good use cases, since you're only getting one row back and the having statement is a filter on that.



      One use case could be that you write code to monitor your licenses for some software; if you have less users than per-user-licenses all's good / you don't want to see the result since you don't care. If you have more users you want to know about it. E.g.



      declare @totalUserLicenses int = 100
      select count(1) NumberOfActiveUsers
      , @totalUserLicenses NumberOfLicenses
      , count(1) - @totalUserLicenses NumberOfAdditionalLicensesToPurchase
      from [dbo].[Users]
      where enabled = 1
      having count(1) > @totalUserLicenses


      Isn't the select irrelevant to the having clause?



      Yes and no. Having is a filter on your aggregated data. Select says what columns/information to bring back. As such you have to ask "what would the result look like?" i.e. Given we've had to effectively apply group by 1 to make use of the having statement, how should SQL interpret select *? Since your table only has one column this would translate to select wage; but we have 5 rows, so 5 different values of wage, and only 1 row in the result to show this.



      I guess you could say "I want to return all rows if their sum is greater than 5; otherwise I don't want to return any rows". Were that your requirement it could be achieved a variety of ways; one of which would be:



      select *
      from [dbo].[_abc]
      where exists
      (
      select 1
      from [dbo].[_abc]
      having sum(wage) > 5
      )


      However, we have to write the code to meet the requirement, rather than expect the code to understand our intent.



      Another way to think about having is as being a where statement applied to a subquery. I.e. your original statement effectively reads:



      select wage
      from
      (
      select sum(wage) sum_of_wage
      from [dbo].[_abc]
      group by 1
      ) singleRowResult
      where sum_of_wage > 5


      That won't run because wage is not available to the outer query; only sum_of_wage is returned.






      share|improve this answer























      • the reason why i gave this small example is that i want to understand the useage of having without the gruoupBy clasue
        – Lijin Durairaj
        Nov 10 '18 at 8:51










      • my question is how can i use the having clause in the table whichi have designed
        – Lijin Durairaj
        Nov 10 '18 at 8:52










      • i have some quesiton regarding the answer which u have posted "The reason your statement doesn't work is because of the select *" 1.the column in the select clause is irrelevant to the having clause, because all the having clause does is provisions us to use the aggregate function, right? if not please explain it, thanks
        – Lijin Durairaj
        Nov 10 '18 at 8:57






      • 1




        Hopefully my updated answer makes more sense... let me know. As someone learning the language your approach is different to most peoples; you're asking how can I use having without using group by as a way to understand it. In standard use cases you'd have a goal in mind first and your question would be how do I achieve this. As mentioned, it's hard to think of many examples where using having without a group by would make sense; so I'd say make a mental note that it's an option, and move on to learning something else; then in future if you hit a use case, hopefully you'll recall this
        – JohnLBevan
        Nov 10 '18 at 9:14
















      4














      The documentation is correct; i.e. you could run this statement:



      select sum(wage) sum_of_all_wages
      , count(1) count_of_all_records
      from [dbo].[_abc]
      having sum(wage) > 5


      The reason your statement doesn't work is because of the select *, which means select every columns' value. When there is no group by, all records are aggregated; i.e. you only get 1 record in your result set which has to represent every record. As such, you can only* include values provided by applying aggregate functions to your columns; not the columns themselves.
      * of course, you can also provide constants, so select 'x' constant, count(1) cnt from myTable would work.



      There aren't many use cases I can think of where you'd want to use having without a group by, but certainly it can be done as shown above.



      NB: If you wanted all rows where the wage was greater than 5, you'd use the where clause instead:



      select * 
      from [dbo].[_abc]
      where wage > 5


      Equally, if you want the sum of all wages greater than 5 you can do this



      select sum(wage) sum_of_wage_over_5 
      from [dbo].[_abc]
      where wage > 5


      Or if you wanted to compare the sum of wages over 5 with those under:



      select case when wage > 5 then 1 else 0 end wage_over_five
      , sum(wage) sum_of_wage
      from [dbo].[_abc]
      group by case when wage > 5 then 1 else 0 end


      See runnable examples here.





      Update based on comments:



      Do you need having to use aggregate functions?



      No. You can run select sum(wage) from [dbo].[_abc]. When an aggregate function is used without a group by clause, it's as if you're grouping by a constant; i.e. select sum(wage) from [dbo].[_abc] group by 1.



      The documentation merely means that whilst normally you'd have a having statement with a group by statement, it's OK to exclude the group by / in such cases the having statement, like the select statement, will treat your query as if you'd specified group by 1



      What's the point?



      It's hard to think of many good use cases, since you're only getting one row back and the having statement is a filter on that.



      One use case could be that you write code to monitor your licenses for some software; if you have less users than per-user-licenses all's good / you don't want to see the result since you don't care. If you have more users you want to know about it. E.g.



      declare @totalUserLicenses int = 100
      select count(1) NumberOfActiveUsers
      , @totalUserLicenses NumberOfLicenses
      , count(1) - @totalUserLicenses NumberOfAdditionalLicensesToPurchase
      from [dbo].[Users]
      where enabled = 1
      having count(1) > @totalUserLicenses


      Isn't the select irrelevant to the having clause?



      Yes and no. Having is a filter on your aggregated data. Select says what columns/information to bring back. As such you have to ask "what would the result look like?" i.e. Given we've had to effectively apply group by 1 to make use of the having statement, how should SQL interpret select *? Since your table only has one column this would translate to select wage; but we have 5 rows, so 5 different values of wage, and only 1 row in the result to show this.



      I guess you could say "I want to return all rows if their sum is greater than 5; otherwise I don't want to return any rows". Were that your requirement it could be achieved a variety of ways; one of which would be:



      select *
      from [dbo].[_abc]
      where exists
      (
      select 1
      from [dbo].[_abc]
      having sum(wage) > 5
      )


      However, we have to write the code to meet the requirement, rather than expect the code to understand our intent.



      Another way to think about having is as being a where statement applied to a subquery. I.e. your original statement effectively reads:



      select wage
      from
      (
      select sum(wage) sum_of_wage
      from [dbo].[_abc]
      group by 1
      ) singleRowResult
      where sum_of_wage > 5


      That won't run because wage is not available to the outer query; only sum_of_wage is returned.






      share|improve this answer























      • the reason why i gave this small example is that i want to understand the useage of having without the gruoupBy clasue
        – Lijin Durairaj
        Nov 10 '18 at 8:51










      • my question is how can i use the having clause in the table whichi have designed
        – Lijin Durairaj
        Nov 10 '18 at 8:52










      • i have some quesiton regarding the answer which u have posted "The reason your statement doesn't work is because of the select *" 1.the column in the select clause is irrelevant to the having clause, because all the having clause does is provisions us to use the aggregate function, right? if not please explain it, thanks
        – Lijin Durairaj
        Nov 10 '18 at 8:57






      • 1




        Hopefully my updated answer makes more sense... let me know. As someone learning the language your approach is different to most peoples; you're asking how can I use having without using group by as a way to understand it. In standard use cases you'd have a goal in mind first and your question would be how do I achieve this. As mentioned, it's hard to think of many examples where using having without a group by would make sense; so I'd say make a mental note that it's an option, and move on to learning something else; then in future if you hit a use case, hopefully you'll recall this
        – JohnLBevan
        Nov 10 '18 at 9:14














      4












      4








      4






      The documentation is correct; i.e. you could run this statement:



      select sum(wage) sum_of_all_wages
      , count(1) count_of_all_records
      from [dbo].[_abc]
      having sum(wage) > 5


      The reason your statement doesn't work is because of the select *, which means select every columns' value. When there is no group by, all records are aggregated; i.e. you only get 1 record in your result set which has to represent every record. As such, you can only* include values provided by applying aggregate functions to your columns; not the columns themselves.
      * of course, you can also provide constants, so select 'x' constant, count(1) cnt from myTable would work.



      There aren't many use cases I can think of where you'd want to use having without a group by, but certainly it can be done as shown above.



      NB: If you wanted all rows where the wage was greater than 5, you'd use the where clause instead:



      select * 
      from [dbo].[_abc]
      where wage > 5


      Equally, if you want the sum of all wages greater than 5 you can do this



      select sum(wage) sum_of_wage_over_5 
      from [dbo].[_abc]
      where wage > 5


      Or if you wanted to compare the sum of wages over 5 with those under:



      select case when wage > 5 then 1 else 0 end wage_over_five
      , sum(wage) sum_of_wage
      from [dbo].[_abc]
      group by case when wage > 5 then 1 else 0 end


      See runnable examples here.





      Update based on comments:



      Do you need having to use aggregate functions?



      No. You can run select sum(wage) from [dbo].[_abc]. When an aggregate function is used without a group by clause, it's as if you're grouping by a constant; i.e. select sum(wage) from [dbo].[_abc] group by 1.



      The documentation merely means that whilst normally you'd have a having statement with a group by statement, it's OK to exclude the group by / in such cases the having statement, like the select statement, will treat your query as if you'd specified group by 1



      What's the point?



      It's hard to think of many good use cases, since you're only getting one row back and the having statement is a filter on that.



      One use case could be that you write code to monitor your licenses for some software; if you have less users than per-user-licenses all's good / you don't want to see the result since you don't care. If you have more users you want to know about it. E.g.



      declare @totalUserLicenses int = 100
      select count(1) NumberOfActiveUsers
      , @totalUserLicenses NumberOfLicenses
      , count(1) - @totalUserLicenses NumberOfAdditionalLicensesToPurchase
      from [dbo].[Users]
      where enabled = 1
      having count(1) > @totalUserLicenses


      Isn't the select irrelevant to the having clause?



      Yes and no. Having is a filter on your aggregated data. Select says what columns/information to bring back. As such you have to ask "what would the result look like?" i.e. Given we've had to effectively apply group by 1 to make use of the having statement, how should SQL interpret select *? Since your table only has one column this would translate to select wage; but we have 5 rows, so 5 different values of wage, and only 1 row in the result to show this.



      I guess you could say "I want to return all rows if their sum is greater than 5; otherwise I don't want to return any rows". Were that your requirement it could be achieved a variety of ways; one of which would be:



      select *
      from [dbo].[_abc]
      where exists
      (
      select 1
      from [dbo].[_abc]
      having sum(wage) > 5
      )


      However, we have to write the code to meet the requirement, rather than expect the code to understand our intent.



      Another way to think about having is as being a where statement applied to a subquery. I.e. your original statement effectively reads:



      select wage
      from
      (
      select sum(wage) sum_of_wage
      from [dbo].[_abc]
      group by 1
      ) singleRowResult
      where sum_of_wage > 5


      That won't run because wage is not available to the outer query; only sum_of_wage is returned.






      share|improve this answer














      The documentation is correct; i.e. you could run this statement:



      select sum(wage) sum_of_all_wages
      , count(1) count_of_all_records
      from [dbo].[_abc]
      having sum(wage) > 5


      The reason your statement doesn't work is because of the select *, which means select every columns' value. When there is no group by, all records are aggregated; i.e. you only get 1 record in your result set which has to represent every record. As such, you can only* include values provided by applying aggregate functions to your columns; not the columns themselves.
      * of course, you can also provide constants, so select 'x' constant, count(1) cnt from myTable would work.



      There aren't many use cases I can think of where you'd want to use having without a group by, but certainly it can be done as shown above.



      NB: If you wanted all rows where the wage was greater than 5, you'd use the where clause instead:



      select * 
      from [dbo].[_abc]
      where wage > 5


      Equally, if you want the sum of all wages greater than 5 you can do this



      select sum(wage) sum_of_wage_over_5 
      from [dbo].[_abc]
      where wage > 5


      Or if you wanted to compare the sum of wages over 5 with those under:



      select case when wage > 5 then 1 else 0 end wage_over_five
      , sum(wage) sum_of_wage
      from [dbo].[_abc]
      group by case when wage > 5 then 1 else 0 end


      See runnable examples here.





      Update based on comments:



      Do you need having to use aggregate functions?



      No. You can run select sum(wage) from [dbo].[_abc]. When an aggregate function is used without a group by clause, it's as if you're grouping by a constant; i.e. select sum(wage) from [dbo].[_abc] group by 1.



      The documentation merely means that whilst normally you'd have a having statement with a group by statement, it's OK to exclude the group by / in such cases the having statement, like the select statement, will treat your query as if you'd specified group by 1



      What's the point?



      It's hard to think of many good use cases, since you're only getting one row back and the having statement is a filter on that.



      One use case could be that you write code to monitor your licenses for some software; if you have less users than per-user-licenses all's good / you don't want to see the result since you don't care. If you have more users you want to know about it. E.g.



      declare @totalUserLicenses int = 100
      select count(1) NumberOfActiveUsers
      , @totalUserLicenses NumberOfLicenses
      , count(1) - @totalUserLicenses NumberOfAdditionalLicensesToPurchase
      from [dbo].[Users]
      where enabled = 1
      having count(1) > @totalUserLicenses


      Isn't the select irrelevant to the having clause?



      Yes and no. Having is a filter on your aggregated data. Select says what columns/information to bring back. As such you have to ask "what would the result look like?" i.e. Given we've had to effectively apply group by 1 to make use of the having statement, how should SQL interpret select *? Since your table only has one column this would translate to select wage; but we have 5 rows, so 5 different values of wage, and only 1 row in the result to show this.



      I guess you could say "I want to return all rows if their sum is greater than 5; otherwise I don't want to return any rows". Were that your requirement it could be achieved a variety of ways; one of which would be:



      select *
      from [dbo].[_abc]
      where exists
      (
      select 1
      from [dbo].[_abc]
      having sum(wage) > 5
      )


      However, we have to write the code to meet the requirement, rather than expect the code to understand our intent.



      Another way to think about having is as being a where statement applied to a subquery. I.e. your original statement effectively reads:



      select wage
      from
      (
      select sum(wage) sum_of_wage
      from [dbo].[_abc]
      group by 1
      ) singleRowResult
      where sum_of_wage > 5


      That won't run because wage is not available to the outer query; only sum_of_wage is returned.







      share|improve this answer














      share|improve this answer



      share|improve this answer








      edited Nov 10 '18 at 9:27

























      answered Nov 10 '18 at 8:42









      JohnLBevan

      14.3k145103




      14.3k145103












      • the reason why i gave this small example is that i want to understand the useage of having without the gruoupBy clasue
        – Lijin Durairaj
        Nov 10 '18 at 8:51










      • my question is how can i use the having clause in the table whichi have designed
        – Lijin Durairaj
        Nov 10 '18 at 8:52










      • i have some quesiton regarding the answer which u have posted "The reason your statement doesn't work is because of the select *" 1.the column in the select clause is irrelevant to the having clause, because all the having clause does is provisions us to use the aggregate function, right? if not please explain it, thanks
        – Lijin Durairaj
        Nov 10 '18 at 8:57






      • 1




        Hopefully my updated answer makes more sense... let me know. As someone learning the language your approach is different to most peoples; you're asking how can I use having without using group by as a way to understand it. In standard use cases you'd have a goal in mind first and your question would be how do I achieve this. As mentioned, it's hard to think of many examples where using having without a group by would make sense; so I'd say make a mental note that it's an option, and move on to learning something else; then in future if you hit a use case, hopefully you'll recall this
        – JohnLBevan
        Nov 10 '18 at 9:14


















      • the reason why i gave this small example is that i want to understand the useage of having without the gruoupBy clasue
        – Lijin Durairaj
        Nov 10 '18 at 8:51










      • my question is how can i use the having clause in the table whichi have designed
        – Lijin Durairaj
        Nov 10 '18 at 8:52










      • i have some quesiton regarding the answer which u have posted "The reason your statement doesn't work is because of the select *" 1.the column in the select clause is irrelevant to the having clause, because all the having clause does is provisions us to use the aggregate function, right? if not please explain it, thanks
        – Lijin Durairaj
        Nov 10 '18 at 8:57






      • 1




        Hopefully my updated answer makes more sense... let me know. As someone learning the language your approach is different to most peoples; you're asking how can I use having without using group by as a way to understand it. In standard use cases you'd have a goal in mind first and your question would be how do I achieve this. As mentioned, it's hard to think of many examples where using having without a group by would make sense; so I'd say make a mental note that it's an option, and move on to learning something else; then in future if you hit a use case, hopefully you'll recall this
        – JohnLBevan
        Nov 10 '18 at 9:14
















      the reason why i gave this small example is that i want to understand the useage of having without the gruoupBy clasue
      – Lijin Durairaj
      Nov 10 '18 at 8:51




      the reason why i gave this small example is that i want to understand the useage of having without the gruoupBy clasue
      – Lijin Durairaj
      Nov 10 '18 at 8:51












      my question is how can i use the having clause in the table whichi have designed
      – Lijin Durairaj
      Nov 10 '18 at 8:52




      my question is how can i use the having clause in the table whichi have designed
      – Lijin Durairaj
      Nov 10 '18 at 8:52












      i have some quesiton regarding the answer which u have posted "The reason your statement doesn't work is because of the select *" 1.the column in the select clause is irrelevant to the having clause, because all the having clause does is provisions us to use the aggregate function, right? if not please explain it, thanks
      – Lijin Durairaj
      Nov 10 '18 at 8:57




      i have some quesiton regarding the answer which u have posted "The reason your statement doesn't work is because of the select *" 1.the column in the select clause is irrelevant to the having clause, because all the having clause does is provisions us to use the aggregate function, right? if not please explain it, thanks
      – Lijin Durairaj
      Nov 10 '18 at 8:57




      1




      1




      Hopefully my updated answer makes more sense... let me know. As someone learning the language your approach is different to most peoples; you're asking how can I use having without using group by as a way to understand it. In standard use cases you'd have a goal in mind first and your question would be how do I achieve this. As mentioned, it's hard to think of many examples where using having without a group by would make sense; so I'd say make a mental note that it's an option, and move on to learning something else; then in future if you hit a use case, hopefully you'll recall this
      – JohnLBevan
      Nov 10 '18 at 9:14




      Hopefully my updated answer makes more sense... let me know. As someone learning the language your approach is different to most peoples; you're asking how can I use having without using group by as a way to understand it. In standard use cases you'd have a goal in mind first and your question would be how do I achieve this. As mentioned, it's hard to think of many examples where using having without a group by would make sense; so I'd say make a mental note that it's an option, and move on to learning something else; then in future if you hit a use case, hopefully you'll recall this
      – JohnLBevan
      Nov 10 '18 at 9:14













      1














      HAVING without GROUP BY clause is perfectly valid but here is what you need to understand:




      • The result will contain zero or one row


        • The implicit GROUP BY will return exactly one row even if the WHERE condition matched zero rows


        • HAVING will keep or eliminate that single row based on the condition



      • Any column in the SELECT clause needs to be wrapped inside an aggregate function

      • You can also specify an expression as long as it is not functionally dependent on the columns


      Which means you can do this:



      SELECT SUM(wage)
      FROM employees
      HAVING SUM(wage) > 100
      -- One row containing the sum if the sum is greater than 5
      -- Zero rows otherwise


      Or even this:



      SELECT 1
      FROM employees
      HAVING SUM(wage) > 100
      -- One row containing "1" if the sum is greater than 5
      -- Zero rows otherwise


      This construct is often used when you're interested in checking if a match for the aggregate was found:



      SELECT *
      FROM departments
      WHERE EXISTS (
      SELECT 1
      FROM employees
      WHERE employees.department = departments.department
      HAVING SUM(wage) > 100
      )
      -- all departments whose employees earn more than 100 in total





      share|improve this answer




























        1














        HAVING without GROUP BY clause is perfectly valid but here is what you need to understand:




        • The result will contain zero or one row


          • The implicit GROUP BY will return exactly one row even if the WHERE condition matched zero rows


          • HAVING will keep or eliminate that single row based on the condition



        • Any column in the SELECT clause needs to be wrapped inside an aggregate function

        • You can also specify an expression as long as it is not functionally dependent on the columns


        Which means you can do this:



        SELECT SUM(wage)
        FROM employees
        HAVING SUM(wage) > 100
        -- One row containing the sum if the sum is greater than 5
        -- Zero rows otherwise


        Or even this:



        SELECT 1
        FROM employees
        HAVING SUM(wage) > 100
        -- One row containing "1" if the sum is greater than 5
        -- Zero rows otherwise


        This construct is often used when you're interested in checking if a match for the aggregate was found:



        SELECT *
        FROM departments
        WHERE EXISTS (
        SELECT 1
        FROM employees
        WHERE employees.department = departments.department
        HAVING SUM(wage) > 100
        )
        -- all departments whose employees earn more than 100 in total





        share|improve this answer


























          1












          1








          1






          HAVING without GROUP BY clause is perfectly valid but here is what you need to understand:




          • The result will contain zero or one row


            • The implicit GROUP BY will return exactly one row even if the WHERE condition matched zero rows


            • HAVING will keep or eliminate that single row based on the condition



          • Any column in the SELECT clause needs to be wrapped inside an aggregate function

          • You can also specify an expression as long as it is not functionally dependent on the columns


          Which means you can do this:



          SELECT SUM(wage)
          FROM employees
          HAVING SUM(wage) > 100
          -- One row containing the sum if the sum is greater than 5
          -- Zero rows otherwise


          Or even this:



          SELECT 1
          FROM employees
          HAVING SUM(wage) > 100
          -- One row containing "1" if the sum is greater than 5
          -- Zero rows otherwise


          This construct is often used when you're interested in checking if a match for the aggregate was found:



          SELECT *
          FROM departments
          WHERE EXISTS (
          SELECT 1
          FROM employees
          WHERE employees.department = departments.department
          HAVING SUM(wage) > 100
          )
          -- all departments whose employees earn more than 100 in total





          share|improve this answer














          HAVING without GROUP BY clause is perfectly valid but here is what you need to understand:




          • The result will contain zero or one row


            • The implicit GROUP BY will return exactly one row even if the WHERE condition matched zero rows


            • HAVING will keep or eliminate that single row based on the condition



          • Any column in the SELECT clause needs to be wrapped inside an aggregate function

          • You can also specify an expression as long as it is not functionally dependent on the columns


          Which means you can do this:



          SELECT SUM(wage)
          FROM employees
          HAVING SUM(wage) > 100
          -- One row containing the sum if the sum is greater than 5
          -- Zero rows otherwise


          Or even this:



          SELECT 1
          FROM employees
          HAVING SUM(wage) > 100
          -- One row containing "1" if the sum is greater than 5
          -- Zero rows otherwise


          This construct is often used when you're interested in checking if a match for the aggregate was found:



          SELECT *
          FROM departments
          WHERE EXISTS (
          SELECT 1
          FROM employees
          WHERE employees.department = departments.department
          HAVING SUM(wage) > 100
          )
          -- all departments whose employees earn more than 100 in total






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 10 '18 at 10:40

























          answered Nov 10 '18 at 10:34









          Salman A

          175k66336424




          175k66336424























              0














              In SQL you cannot return aggregate functioned columns directly. You need to group the non aggregate fields



              As shown below example



               USE AdventureWorks2012 ;  
              GO
              SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
              FROM Sales.SalesOrderDetail
              GROUP BY SalesOrderID
              HAVING SUM(LineTotal) > 100000.00
              ORDER BY SalesOrderID ;


              In your case you don't have identity column for your table it should come as below



              Alter _abc
              Add Id_new Int Identity(1, 1)
              Go





              share|improve this answer


























                0














                In SQL you cannot return aggregate functioned columns directly. You need to group the non aggregate fields



                As shown below example



                 USE AdventureWorks2012 ;  
                GO
                SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
                FROM Sales.SalesOrderDetail
                GROUP BY SalesOrderID
                HAVING SUM(LineTotal) > 100000.00
                ORDER BY SalesOrderID ;


                In your case you don't have identity column for your table it should come as below



                Alter _abc
                Add Id_new Int Identity(1, 1)
                Go





                share|improve this answer
























                  0












                  0








                  0






                  In SQL you cannot return aggregate functioned columns directly. You need to group the non aggregate fields



                  As shown below example



                   USE AdventureWorks2012 ;  
                  GO
                  SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
                  FROM Sales.SalesOrderDetail
                  GROUP BY SalesOrderID
                  HAVING SUM(LineTotal) > 100000.00
                  ORDER BY SalesOrderID ;


                  In your case you don't have identity column for your table it should come as below



                  Alter _abc
                  Add Id_new Int Identity(1, 1)
                  Go





                  share|improve this answer












                  In SQL you cannot return aggregate functioned columns directly. You need to group the non aggregate fields



                  As shown below example



                   USE AdventureWorks2012 ;  
                  GO
                  SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
                  FROM Sales.SalesOrderDetail
                  GROUP BY SalesOrderID
                  HAVING SUM(LineTotal) > 100000.00
                  ORDER BY SalesOrderID ;


                  In your case you don't have identity column for your table it should come as below



                  Alter _abc
                  Add Id_new Int Identity(1, 1)
                  Go






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 10 '18 at 8:44









                  PrathapG

                  534418




                  534418






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.





                      Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                      Please pay close attention to the following guidance:


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53237309%2fuse-of-having-without-group-by-not-working-as-expected%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Guess what letter conforming each word

                      Port of Spain

                      Run scheduled task as local user group (not BUILTIN)