Posted on

SELECT Statement Subquery Example (MySQL/SQL Server)

How to use a subquery in a SELECT statement (SQL Server/MySQL)

SELECT Statement Subquery Example - SQL ServerSELECT Statement Subquery Example - MySQLOften I need to produce a query over a parent table and get a total of values from a child table. This can be accomplished with a single query that utilizes a subquery within the SELECT statement. In the following example, we’re selecting parent values from a table named tbl_loans and utilizing a subquery to select the total of the amount paid toward principal from a child table named tbl_loanpayments. For each row in the table tbl_loans, there could be multiple child rows in the table tbl_loanpayments. We want the amount paid to the principal for all loan payment rows that have an amount paid not equal zero.

The following example will work in SQL Server or MySQL.
Continue reading “SELECT Statement Subquery Example (MySQL/SQL Server)”

Posted on

SQL Subtotal With Detail Example (SQL Server/MySQL)

How to subtotal a result while retaining detail (SQL Server/MySQL)

SQL Subtotal With Detail Example - SQL ServerSQL Subtotal With Detail Example - MySQLI recently needed to produce a query with detail and have it subtotal by employee, by date. Subtotals can be difficult if you have data that varies within the subtotalled group.

The work around is to union queries. One query with the detail and another query with the subtotal by the control break you need. We then use a case condition in the order by clause to cause our subtotal to appear where we want it in the result.

The following example will work in SQL Server or MySQL.
Continue reading “SQL Subtotal With Detail Example (SQL Server/MySQL)”