Get COUNT for each ID and ID itself in table - mysql

Question

I wanted to know how often an invoiceID appears among the invoiceLines that this invoice has. SO for example

+++++++++++++++++++++++++++++++++ + invoiceID+ COUNT(invoiceLines)+ +++++++++++++++++++++++++++++++++ + 1 + 10 + +++++++++++++++++++++++++++++++++ + 2 + 17 + +++++++++++++++++++++++++++++++++ + 3 + 5 + +++++++++++++++++++++++++++++++++

etc.

My current query looks like this:

select DISTINCT invoice.invoiceID, COUNT(invoiceLine.invoiceLineID) from invoice join invoiceLine on invoiceLine.fk_invoiceID = invoice.invoiceID;

Though this won't work, as I'm selecting a none aggregated value and an aggregated value. Hence the error message:

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'wms_new.invoice.invoiceID'; this is incompatible with sql_mode=only_full_group_by

Does anybody know how to pull off something like this without disabling "sql_mode=only_fill_group_by"

remove DISTINCT and add GROUP BY invoice.invoiceID to the end of your query. — Nick
– Nick, Commented Aug 23, 2019 at 8:19

Caius Jard · Accepted Answer · 2019-08-23 08:25:55Z

This requirement can actually be answered entirely by the invoice lines table. You don't need the join to invoice:

select fk_invoiceID, COUNT(*) from invoiceLine group by fk_invoiceID

In your current MySQL mode every column in the select list that is NOT an aggregating function (count, min, max, sum, avg etc) must appear in the group by also

The scenario where you would use DISTINCT are quite narrow; seek to avoid using it. It's not required in a grouping query because by their very nature they produce outputs where rows are a unique set of values

Thank you for the solution, I did notice that too after seeing Nicks comment. I'll mark your answer as correct as soon as I can.

Collectives™ on Stack Overflow

Get COUNT for each ID and ID itself in table - mysql

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related