Robert Farr Robert Farr - 2 months ago 7
MySQL Question

How to merge these results into one row

I have this code below. I am trying to merge the rows based on date.

SELECT

TICKETS.TICKETID,
RECEIPTS.DATENEW,
TAXCATEGORIES.NAME = 'GCT' as GCT,
TAXCATEGORIES.NAME = 'Tax 25%' as Tax25,
TAXLINES.AMOUNT,

SUM(TAXLINES.AMOUNT) AS TOTAL,
SUM(CASE WHEN taxcategories.NAME = 'GCT' THEN taxlines.AMOUNT ELSE 0 END) AS GCTTOTAL,
SUM(CASE WHEN taxcategories.NAME = 'Tax 25%' THEN taxlines.AMOUNT ELSE 0 END) AS TAX25TOTAL

FROM
RECEIPTS,
TAXLINES,
TAXES,
TAXCATEGORIES,
TICKETS,
PAYMENTS

WHERE
PAYMENTS.RECEIPT = RECEIPTS.ID
AND RECEIPTS.ID = TAXLINES.RECEIPT
AND RECEIPTS.ID = TICKETS.ID
AND TAXLINES.TAXID = TAXES.ID
AND TAXES.CATEGORY = TAXCATEGORIES.ID
AND DATENEW >= '2016-07-14 00:00:00' AND DATENEW <= '2016-07-14 23:00:00'


GROUP BY gct, Tax25, CAST(RECEIPTS.DATENEW AS DATE)


The Result of the query is attached in the screenshot below:
enter image description here

Now I need help to merge those rows that have the same date into one row. I am not sure where I am going wrong, I've tried a series of joins but I'm coming up blank.

Answer

It appears that the records with the same date actually differ in their values for the GCT and Tax25 columns. If you remove these columns from the GROUP BY clause, and instead aggregate them in the SELECT list, you would be left with a single record for the duplicate dates.

Note that in the query below I have replaced your implicit joins (using a comma-separated list of tables in the FROM clause) with explicit inner joins, with the join criteria in the ON clause. This is the standard way of writing queries now, and it makes them much easier to read. If INNER JOIN is too restrictive, then perhaps you intended to use LEFT JOIN instead.

SELECT TICKETS.TICKETID,
       RECEIPTS.DATENEW,
       MAX(TAXCATEGORIES.NAME) = 'GCT' as GCT,         -- one value per date
       MAX(TAXCATEGORIES.NAME) = 'Tax 25%' as Tax25,   -- one value per date
       TAXLINES.AMOUNT,
       SUM(TAXLINES.AMOUNT) AS TOTAL,
       SUM(CASE WHEN taxcategories.NAME = 'GCT' THEN taxlines.AMOUNT ELSE 0 END) AS GCTTOTAL,
       SUM(CASE WHEN taxcategories.NAME = 'Tax 25%' THEN taxlines.AMOUNT ELSE 0 END) AS TAX25TOTAL
FROM RECEIPTS
INNER JOIN TAXLINES
    ON RECEIPTS.ID = TAXLINES.RECEIPT
INNER JOIN TAXES
    ON TAXLINES.TAXID = TAXES.ID
INNER JOIN TAXCATEGORIES
    ON TAXES.CATEGORY = TAXCATEGORIES.ID
INNER JOIN TICKETS
    ON RECEIPTS.ID = TICKETS.ID
INNER JOIN PAYMENTS
    ON PAYMENTS.RECEIPT = RECEIPTS.ID
WHERE DATENEW >= '2016-07-14 00:00:00' AND
      DATENEW <= '2016-07-14 23:00:00'
GROUP BY CAST(RECEIPTS.DATENEW AS DATE)
Comments