Gotchas:
BETWEEN with dates is inclusive, but if you have date time fields it will only consider up to the date AND time given so no time means the upper cutoff will be midnight at the start of the day.
SELECT COUNT (*) will do a table scan and can be expensive. Most RDBMS will have a method to read the statistics which includes the rowcount. Alternatively count the primary or clustered key as this will only be an index scan.
Don't ignore SELECT COUNT DISTINCT (field) which effectively does a GROUP BY on the field or fields and then counts the group's. Or SELECT DISTINCT field1, field2 which also does a implicit group by. It is a good way to check for Cartesian joins: if the distinct returned fewer rows then you have a bad join somewhere.
The aggregate functions can be used with the OVER() clause which defines a context for the aggregate. E.g.
Select customer.ID, customer.name,AVG(order.value) OVER (PARTITION BY customer_id)
Means you don't need a GROUP BY that includes all the customer fields.
Gotchas:
BETWEEN with dates is inclusive, but if you have date time fields it will only consider up to the date AND time given so no time means the upper cutoff will be midnight at the start of the day.
SELECT COUNT (*) will do a table scan and can be expensive. Most RDBMS will have a method to read the statistics which includes the rowcount. Alternatively count the primary or clustered key as this will only be an index scan.
Don't ignore SELECT COUNT DISTINCT (field) which effectively does a GROUP BY on the field or fields and then counts the group's. Or SELECT DISTINCT field1, field2 which also does a implicit group by. It is a good way to check for Cartesian joins: if the distinct returned fewer rows then you have a bad join somewhere.
The aggregate functions can be used with the OVER() clause which defines a context for the aggregate. E.g.
Select customer.ID, customer.name,AVG(order.value) OVER (PARTITION BY customer_id)
Means you don't need a GROUP BY that includes all the customer fields.
Thank you for shearing your knowledge