StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><code>GROUP BY</code> would probably allow the SQL engine to better optimize your query but would make it harder to read due to the large number of grouping parameters.</p> <p>Another option as recommended by the <a href="http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx" rel="nofollow noreferrer">SQL Team</a> is to consider using Sub queries. This can often make the <code>GROUP BY</code> statements much simpler and makes the overall query much easier to read.</p> <h3>Using a Sub query:</h3> <pre><code>SELECT orders.billerID, orders.invoiceDate, orders.txnID, orders.bName, orders.bStreet1, orders.bStreet2, orders.bCity, orders.bState, orders.bZip, orders.bCountry, orders.sName, orders.sStreet1, orders.sStreet2, orders.sCity, orders.sState, orders.sZip, orders.sCountry, orders.paymentType, orders.invoiceNotes, orders.pFee, orders.shipping, orders.tax, orders.reasonCode, orders.txnType, orders.customerID, customers.firstName AS firstName, customers.lastName AS lastName, customers.businessName AS businessName, orderStatus.statusName AS orderStatus, orderItem.fees + orders.shipping + orders.tax AS orderTotal, orderItem.fees + orders.shipping + orders.tax - payments.amount AS orderBalance FROM orders LEFT JOIN customers ON orders.customerID = customers.id LEFT JOIN orderStatus ON orders.orderStatus = orderStatus.id LEFT JOIN ( SELECT orderID, SUM(itemPrice * itemQuantity) as fees FROM orderItems GROUP BY orderID ) orderItems ON orderItems.orderID = orders.id LEFT JOIN ( SELECT orderID, SUM(amount) as amount FROM payments GROUP BY orderID ) payments ON payments.orderID = orders.id </code></pre> <h3>Using a <code>GROUP BY</code>:</h3> <pre><code>SELECT orders.billerID, orders.invoiceDate, orders.txnID, orders.bName, orders.bStreet1, orders.bStreet2, orders.bCity, orders.bState, orders.bZip, orders.bCountry, orders.sName, orders.sStreet1, orders.sStreet2, orders.sCity, orders.sState, orders.sZip, orders.sCountry, orders.paymentType, orders.invoiceNotes, orders.pFee, orders.shipping, orders.tax, orders.reasonCode, orders.txnType, orders.customerID, customers.firstName AS firstName, customers.lastName AS lastName, customers.businessName AS businessName, orderStatus.statusName AS orderStatus, SUM(orderItems.itemPrice * orderItems.itemQuantity) + orders.shipping + orders.tax AS orderTotal, SUM(orderItems.itemPrice * orderItems.itemQuantity) + orders.shipping + orders.tax - SUM(payments.amount) AS orderBalance FROM orders LEFT JOIN customers ON orders.customerID = customers.id LEFT JOIN orderStatus ON orders.orderStatus = orderStatus.id LEFT JOIN orderItems ON orderItems.orderID = orders.id LEFT JOIN payments ON payments.orderID = orders.id GROUP BY orders.billerID, orders.invoiceDate, orders.txnID, orders.bName, orders.bStreet1, orders.bStreet2, orders.bCity, orders.bState, orders.bZip, orders.bCountry, orders.sName, orders.sStreet1, orders.sStreet2, orders.sCity, orders.sState, orders.sZip, orders.sCountry, orders.paymentType, orders.invoiceNotes, orders.pFee, orders.shipping, orders.tax, orders.reasonCode, orders.txnType, orders.customerID, customers.firstName, customers.lastName, customers.businessName, orderStatus.statusName </code></pre> <h3><code>GROUP BY</code> Explained:</h3> <p>You can thing of <code>GROUP BY</code> as collecting records together that have similar data. For my example I am going to use a simple produce table with <code>Category</code>, <code>Name</code> and <code>Price</code> columns. If I group the data by <code>Category</code> I can aggregate ( i.e. <code>SUM</code>, <code>COUNT</code>, <code>MIN</code>, <code>MAX</code>, etc.) based on any of the other columns. Since I am grouping by the <code>Category</code> column the resulting records will have a unique value for <code>Category</code>. Any of the other columns might be return different value and therefore cannot be included in the select statement.</p> <p><strong>Name, Category, Price</strong><br> Green Peppers, Peppers, 1.50<br> Orange Peppers, Peppers, 2.50<br> Yellow Peppers, Peppers, 2.50<br> Lemons, Citrus, 1.00<br> Oranges, Citrus, 1.00<br> Limes, Citrus, 1.00 </p> <pre><code>SELECT Category, /* This is unique because it is in the GROUP BY clause */ AVG(Price) AS AveragePrice, MAX(Price) AS MaxPrice, MIN(Price) AS MinPrice /* , Name */ /* This is invalid because it is not in the GROUP BY clause */ /* The values are not unique so SQL does not know what to return */ FROM Produce GROUP BY Category </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