StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The aliasing is at the query level, so your second example is fine.</p> <p>The reference to the tables in the from clause makes these correlated subqueries, in case you want to read more about that topic.</p> <p>In general, I recommend that you move the queries to the from clause and manage them as joins:</p> <pre><code>Select p.purchaseid, p.purchasedate, s.storename, c.customerid, pdate.cnt as [Store Daily Total Purchases], pcust.cnt as [Customer Daily Total Purchases] from purchases p inner join customer c on p.customerid = c.customerid join store s on p.storeid = s.storeid left outer join (select s2.storeid, p2.purchasedate, count(p2.purchaseid) from purchases p2 inner join store s2 on p2.storeid = s2.storeid group by s2.storeid, p2.purchasedate ) pdate on pdate.purchasedate = p.purchasedate and pdate.storeid = s.storeid left outer join (select s2.customerid, p2.purchasedate, count(p2.purchaseid) from purchases p2 inner join store s2 on p2.storeid = s2.storeid group by s2.storeid, p2.purchasedate ) pcust on pcust.purchasedate = p.purchasedate and pcust.customerid= s.customerid </code></pre> <p>However, in your case, I think there is a simpler solution just using aggregation:</p> <pre><code>Select p.purchaseid, p.purchasedate, s.storename, c.customerid, count(*) over (partition by s.storeid, p.purchasedate) as as [Store Daily Total Purchases], count(*) over (partition by c.customerid, p.purchasedate) as [Customer Daily Total Purchases] from purchases p inner join customer c on p.customerid = c.customerid join store s on p.storeid = s.storeid </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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