Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I wouldn't recommend the <code>HAVING</code> keyword for newbies, it is <a href="http://www.dcs.warwick.ac.uk/~hugh/TTM/HAVING-A-Blunderful-Time.html" rel="nofollow">essentially for legacy purposes</a>. </p> <p>The following is more verbose but could be easier to understand and therefore maintain (I've used a <a href="http://msdn.microsoft.com/en-us/library/ms190766.aspx" rel="nofollow">CTE</a> for the table <code>CUSTOMER_RENOVATION_TALLIES</code> but it could be a <a href="http://msdn.microsoft.com/en-us/library/ms187956.aspx" rel="nofollow"><code>VIEW</code></a> :</p> <pre><code>WITH CUSTOMER_RENOVATION_TALLIES ("Customer-ID", Tally) AS ( SELECT [Customer-ID], COUNT(*) AS Tally FROM RENOVATION GROUP BY [Customer-ID] ) SELECT c."Customer-ID", c.name, r.Tally FROM CUSTOMER AS c INNER JOIN CUSTOMER_RENOVATION_TALLIES AS r ON c."Customer-ID" = r."Customer-ID"; </code></pre> <p>If you want to include a tally of zero for customers without renovations then <code>UINON</code> this set to the above resultset e.g. </p> <pre><code>WITH CUSTOMER_RENOVATION_TALLIES ("Customer-ID", Tally) AS ( SELECT [Customer-ID], COUNT(*) AS Tally FROM RENOVATION GROUP BY [Customer-ID] ) SELECT c."Customer-ID", c.name, r.Tally FROM CUSTOMER AS c INNER JOIN CUSTOMER_RENOVATION_TALLIES AS r ON c."Customer-ID" = r."Customer-ID" UNION SELECT c."Customer-ID", c.name, 0 AS Tally FROM CUSTOMER AS c WHERE NOT EXISTS ( SELECT * FROM CUSTOMER_RENOVATION_TALLIES AS r WHERE c."Customer-ID" = r."Customer-ID" ); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSQL Query question
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USonedaywhen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    1. COI disagree with your statement that "it is essentially for legacy purposes" and even more with the article you linked to which says that "`HAVING` means exactly the same as `WHERE`, but has different rules concerning what can be written in the condition that follows it and in the `SELECT` clause that physically precedes it." There is a very important difference between `WHERE` and `HAVING` that makes `HAVING` useful for more than just "legacy purposes": `WHERE` filters records before grouping is applied, and `HAVING` applies filtering on the aggregates _after_ grouping is applied.
      singulars
      1. PO
      1. USBoris Nikolaevich
    2. COThe fact that they do different things necessitates the different terminology; how much **more** confusing would it be if both criteria operators were called `WHERE`?
      singulars
      1. PO
      1. USBoris Nikolaevich
    3. CO@Boris Nikolaevich: it could be you missed the main point i.e. quote "if only the System R team had realized that their language was relationally incomplete without support for derived tables in the FROM clause, then they (and after them, Larry Ellison with his Oracle that brought SQL to the unsuspecting public at large) might have included such support and thus allowed their users... not to have to learn HAVING." Of course, it could be that you understand fully yet hold a different opinion, which is fine by me :)
      singulars
      1. PO
      1. USonedaywhen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload