Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The quick answer is to wrap your query inside another one, </p> <pre><code>SELECT ConfInt , Conference , SUM(Ordered) AS Ordered , SUM(Approved) As Approved , SUM(PickedUp) AS PickedUp , SUM(Qty) AS Qty FROM ( &lt;your UNION query here&gt; ) GROUP BY ConfInt, Conference </code></pre> <hr> <p>This is not the only way to achieve the result set, but its the quickest fix to meet the specified requirements.</p> <p>As an alternative, I believe these queries will return equivalent results:</p> <p>We could use a correlated subquery in the SELECT list to get Qty:</p> <pre><code>;WITH q AS ( SELECT B.ConfInt , B.Conference , SUM(o.NumberOfFansRequested) AS Ordered , SUM(o.Qty_Fans_Approved) AS Approved , SUM(o.Qty_Fans_PickedUp) AS PickedUp FROM dbo.Conferences as B LEFT JOIN dbo.Cool_Fan_Order o ON o.Conference_ID = B.ConfInt WHERE B.ProjectCool = 1 GROUP BY B.ConfInt, B.Conference ) SELECT q.ConfInt , q.Conference , q.Ordered , q.Approved , q.PickedUp , ( SELECT SUM(v.Qty) FROM dbo.Case_Table t JOIN dbo.Case_Visit_Payments v ON v.Case_ID = t.Case_ID WHERE t.Conference_ID = q.ConfInt AND v.Item_ID = 15 ) AS Qty FROM q ORDER BY q.ConfInt, q.Conference </code></pre> <hr> <p>Or, we could use LEFT JOIN operation on the two queries, rather than UNION. (We know that the query referencing Cool_Fan_Order can be the LEFT side of the outer join, because we know that it returns at least as many rows as the other query. (Basically, we know that the other query can't return values of ConfInt and Conference that aren't in the Cool_Fan_Order query.)</p> <pre><code>;WITH p AS ( SELECT A.ConfInt , A.Conference , SUM(v.Qty) AS Qty FROM dbo.Conferences as A JOIN dbo.Case_Table t ON t.Conference_ID = A.ConfInt JOIN dbo.Case_Visit_Payments v ON v.Case_ID = t.Case_ID WHERE A.ProjectCool = 1 AND v.Item_ID = 15 GROUP BY A.ConfInt, A.Conference ) , q AS ( SELECT B.ConfInt , B.Conference , SUM(o.NumberOfFansRequested) AS Ordered , SUM(o.Qty_Fans_Approved) AS Approved , SUM(o.Qty_Fans_PickedUp) AS PickedUp FROM dbo.Conferences as B LEFT JOIN dbo.Cool_Fan_Order o ON B.ConfInt = o.Conference_ID WHERE B.ProjectCool = 1 GROUP BY B.ConfInt, B.Conference ) SELECT q.ConfInt , q.Conference , q.Ordered , q.Approved , q.PickedUp , p.Qty FROM q LEFT JOIN p ON p.ConfInt = q.ConfInt AND p.Conference = q.Conference ORDER BY q.ConfInt, q.Conference </code></pre> <p>The choice between those three (they all return an equivalent resultset under all conditons), boils down to readability and maintainability, and performance. On large enough rowsets, there may be some observable performance differences between the three statements.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to remove duplicating rows from union statement
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USspencer7593
    1. USspencer7593
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to remove duplicating rows from union statement
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe Correlated Sub-query did the trick. It worked perfectly. Thanks a bunch.
      singulars
      1. PO
      1. USGregory Hampton
    2. COBTW: The Common Table Expression isn't required for statement with the correlated subquery in the SELECT list; the CTE could just as easily be replaced with an inline view (in the FROM clause), which is what we would need to do if this were MySQL.
      singulars
      1. PO
      1. USspencer7593
 

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