PhraseryPhrasery homeGet Phrasery
Data & SQL21 categories

Check my aggregation for grouping traps

Any query with GROUP BY that feeds a number someone will act on.

LongSQLaggregationcorrectness
Review the aggregation in this query specifically for grouping mistakes.

Check:
- Does every non-aggregated column in the SELECT appear in the GROUP BY, and is each one functionally dependent on the group key? A column that varies within a group will silently pick an arbitrary value in some dialects.
- Are groups with zero rows missing from the output entirely? If I need zeroes, I need a calendar or dimension table joined in.
- Is HAVING doing work that belongs in WHERE, or the reverse?
- Is COUNT(*) counting rows when I meant distinct entities?
- Am I averaging a ratio that should be a ratio of sums?
- Does a filter on an aggregate remove groups I intended to keep at zero?

For each problem, show the corrected fragment. Where the aggregation is right, say so and move on.

Query:
What each output row should represent:

Making it yours

  • Paste your own material where the prompt asks for it. Everything above that line is instruction, not content.
  • Delete any rule that does not apply to you. A shorter prompt that fits beats a thorough one that does not.
  • If the answer comes back vague, add a line saying what you do not want. Constraints work better than encouragement.

Correctness checks. Catching the join that duplicates rows, the NULL that drops them, and the GROUP BY that lies.

Related prompts

Check this query before I run itBefore running anything expensive, destructive, or going into a report.Find where this join is duplicating rowsYour totals are suspiciously round multiples of what they should be.Hunt every NULL trap in this queryWhen a count came back lower than expected and nothing looks wrong.Can any of these joins multiply rows?Totals look inflated and you suspect the join before the data.Am I counting the right thing?A count feeds a number someone will act on.Where could NULL change this result?A count came back lower than expected and nothing looks wrong.

Keep this one

Phrasery saves prompts with a right-click and puts them back the same way. Add this to your own library in one click.

Add to your browser — free