

Probable cause



asked 27 Jan '12, 15:20

Siger%20Matt's gravatar image

Siger Matt
accept rate: 13%



WHERE COUNT() > 5 GROUP BY <table.column>


  1. Compute the Cartesian product of all of the table expressions in the FROM clause
  2. Apply the conditions in the WHERE clause to filter those rows that fail to satisfy the search condition
  4. If a HAVING clause exists, filter those grouped rows so that only the desired grouped rows remain
  5. ORDER the result by the ORDER BY clause if it exists




GROUP BY <table.column> HAVING COUNT() > 5


permanent link

answered 27 Jan '12, 16:20

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

UPDATE t SET SUM ( c ) = 0; would you like that done? Set c to zero in every row? Set alternating rows to random positive/negative numbers so the sum remains zero?





permanent link

answered 27 Jan '12, 16:30

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

I run into the situation infrequently and most of the time the message seems right and I understand the logic, but in some situations I think it would still work.

One example: I have a one to many relationship between two tables, such as a bill and its line items.

I might try to write a procedure that totals the line items for a field on the bill with a statement that says:

update Bill
left outer join BillItems on BillItems.BillID = Bill.BillID
set Bill.Total = sum(BillItems.Total)
where BillID = X

The way I read the above, it would update the Bill.Total with the total of the list from the join, doing it each time for each item in the list, but I would not care because it would be the same total and I would not need a second statement to pull the total into a variable and then update.

There is certainly the argument that two statements are better than N updates without knowing how many are in the list, but it seems like the above is writing what I want to happen.

Probably more of an exception that would behave correctly but I thought I would get some more information.

(27 Jan '12, 17:31) Siger Matt
Replies hidden

The way that statement reads, it is understandable what the goal may be.

As far as why it doesn't work (is prohibited)...

First, consider what would happen if you executed...

    Bill.BillID = BillItems.BillID'd get the total of all BillItems including those not on the current bill. Without a GROUP BY, the aggregate will hit all rows. There's no easily defined way to specify GROUP BY and integrate it into the UPDATE statement, so you need to get creative:

    (SELECT BillID, SUM(Total) FROM BillItems GROUP BY BillID) bi
        ON b.BillID = bi.BillID
    SET b.Total = bi.Total
    WHERE b.BillID = X
(27 Jan '12, 19:50) Tyson Lewis

I see. Thank you for that example. I will give that a try next time.

(31 Jan '12, 09:16) Siger Matt

An easier (though possibly less performant) way would be to use a correlated subquery, such as (not tested!):

SET Total =
   (SELECT SUM(Total) FROM BillItems bi WHERE bi.BillID = b.BillID)
(31 Jan '12, 09:45) Volker Barth
Your answer
toggle preview

Follow this question

By Email:




Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • numbered list: 1. Foo 2. Bar
  • basic HTML tags are also supported

Question tags:


question asked: 27 Jan '12, 15:20

question was seen: 11,106 times

last updated: 31 Jan '12, 09:45