0

I have a table called invoice and I have a query that will give me the sum of all invoice amounts for a given account where the invoice has a status of PAID between a certain date range.

Below is the schema and query.

Schema:

CREATE TABLE invoice (
    id             INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    agreement      BOOLEAN NOT NULL DEFAULT 0,
    invoice_number VARCHAR(255) NOT NULL,
    status         VARCHAR(255) NOT NULL INDEX,
    status_text    VARCHAR(255) NOT NULL,
    currency       VARCHAR(255) NOT NULL,
    amount         INT(10) UNSIGNED NOT NULL,
    description    VARCHAR(500) NOT NULL,
    --
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP INDEX,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    --
    PRIMARY KEY (id),
    UNIQUE (invoice_number)
);

CREATE TABLE account_invoice (
    account_id INT(10) UNSIGNED NOT NULL,
    invoice_id INT(10) UNSIGNED NOT NULL,
    --
    INDEX (invoice_id, account_id),
    PRIMARY KEY (account_id, invoice_id),
    FOREIGN KEY (account_id) REFERENCES account (id) ON DELETE CASCADE,
    FOREIGN KEY (invoice_id) REFERENCES invoice (id) ON DELETE CASCADE
);

Query:

SELECT SUM(invoice.amount)
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL
    AND invoice.updated_at >= ? 
    AND invoice.updated_at <= ?
    AND invoice.status = "PAID"

Let's assume our date range starts on Jan 01 2018, ends on Jan 02 2018.

Let's also assume I have five invoices:

  1. updated_at is Jan 01 2018, amount is 1000
  2. updated_at is Jan 01 2018, amount is 2000
  3. updated_at is Jan 02 2018, amount is 50
  4. updated_at is Jan 03 2018, amount is 100
  5. updated_at is Jan 04 2018, amount is 100

Given the data above, the query above will give me a result of 3050. Perfect.

Now what I want to do is take the date range, and get a list, as opposed to the SUM(amount) of matching invoices. For each invoice matched against the query, I want to take its date and amount.

As such, the result set should look like this:

[
   {value: 1000, date: "Jan 01 2018"},
   {value: 2000, date: "Jan 01 2018"},
   {value: 50,   date: "Jan 02 2018"}
]

Using that result set, I want to be able to make a second condition to shrink down the result set further based on on an interval of time, e.g. daily, weekly, monthly, yearly.

So if I use a daily interval from the result set above, I should get this:

[
   {value: 3000, date: "Jan 01 2018"},
   {value: 50,   date: "Jan 02 2018"}
]

If I use a weekly interval, I should get this:

[
   {value: 3050, date: "Jan 01 2018"},
]

...and so on. As you see it just essentially does the sum logic of my first query on the result set of my second query.

My current attempt at the first part (no interval logic) is the following query:

SELECT invoice.amount AS value, invoice.updated_at AS date
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL
    AND invoice.updated_at >= ? 
    AND invoice.updated_at <= ?
    AND invoice.status = "PAID"
GROUP BY invoice.id

This does not give me the result I'm looking for, so I can't hope to move to the second part with intervals yet. Any guidance would be appreciated.

UPDATE:

SELECT invoice.amount, %s AS "invoice.updated_at"
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL
    AND invoice.updated_at >= ? 
    AND invoice.updated_at < ?
    AND invoice.status = "PAID"
GROUP BY invoice.id, invoice.amount, FLOOR(TO_DAYS(invoice.updated_at) - 2) / 7
  • Why are you grouping by invoice.id? – Lennart Dec 25 '18 at 5:47
  • @Lennart I get this error if I don't add it: Error 1140: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'redacted.invoice.updated_at'; this is incompatible with sql_mode=only_full_group_by – Lansana Dec 25 '18 at 5:50
  • But there is no aggregate function in your current attempt? – Lennart Dec 25 '18 at 5:55
  • Yeah it's weird, it's a new MySQL 8 thing I believe, didn't used to get this error. Can easily ignore it by updating a global config or by naming all my columns in my SELECT (which I'm doing) so not sure why the error is appearing at all. – Lansana Dec 25 '18 at 5:56
  • If thats the exact query your running, it looks like a bug. Can you create a small verifiable example in db<>fiddle or similar? – Lennart Dec 25 '18 at 6:01
1

Your first resultset comes from

... WHERE updated_at >= ...
      AND updated_at <  ...

Your second resultset comes from adding this on

... GROUP BY DATE(updated_at)

The third ("weekly") gets trickier:

... GROUP BY FLOOR(TO_DAYS(updated_at) - 2) / 7

(Where the "-2" is adjusted to make a "week" start on Sunday or Monday or whatever you prefer.)

Yearly:

... GROUP BY YEAR(updated_at)

Monthly:

... GROUP BY LEFT(updated_at, 7)

(because the LEFT will give you something like the string "2011-03").

  • Yearly are easy with this example, though I wonder how months would be handled given that not every month has 31 days. – Lansana Dec 26 '18 at 3:13
  • @Lansana - I added yearly and monthly – Rick James Dec 26 '18 at 3:16
  • Having to run a query like GROUP BY invoice.id, invoice.amount, DATE(invoice.updated_at) to avoid the Error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'redacted.invoice.amount' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by error. But this seems to give me a bad result, the group by seems to ignore the function on the updated_at column. – Lansana Dec 27 '18 at 3:22
  • @Lansana - Let's see the whole SELECT. – Rick James Dec 27 '18 at 6:00
  • Updated question with an example query. – Lansana Dec 27 '18 at 6:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.