ex26.md

Exercise: 26

Find out the average price of PCs and laptops produced by maker A.

Result set: one overall average price for all items.

Solution

WITH prices AS (
		SELECT price
		FROM pc
			LEFT JOIN product ON pc.model = product.model
		WHERE product.maker = 'A'
		UNION ALL
		SELECT price
		FROM laptop
			LEFT JOIN product ON laptop.model = product.model
		WHERE product.maker = 'A'
	)
SELECT AVG(price)
FROM prices

References

1. https://stackoverflow.com/questions/37820635/getting-wrong-average-in-a-sql-query

2. https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15