CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Златко Ушев
Златко Ушев

Reputation: 27

How do I remove not needed information from table?

im trying to make a column for month 1, 2 and 3 and put the invoice's total in that column. I have made that successfully, but then below there are more columns with zeroes for every column, aka it shows me the column for every invoice, but I want to get ONLY the invoices that have been made in month 1,2 and 3 and don't show the rest. How do I do that?

what I get:

invoice_id | total | firstMonth | secondMonth | thirdMonth |

1              33.4      0            0           33.4

2            3434.5     0             0             0 <=====



what I want:

invoice_id | total | firstMonth | secondMonth | thirdMonth |

1            33.4         0           0           33.4

Upvotes: 1

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Please learn about types. Single quotes should be used for string and date constants -- and not for column identifiers or numbers.

Then, I think you just want to filter the results correctly:

SELECT i.id AS invoice_ID , i.total AS TOTAL,
      (CASE WHEN month(i.datetime) = 1 THEN ABS(i.total) ELSE 0 END) AS firstMonth,
      (CASE WHEN month(i.datetime) = 2 THEN ABS(i.total) ELSE 0 END) AS secondMonth,
      (CASE WHEN month(i.datetime) = 3 THEN ABS(i.total) ELSE 0 END) AS thirdMonth
FROM invoice i JOIN
     store s
     ON i.store_id = s.id JOIN
     company c
     ON s.company_id = c.id
WHERE c.name = 'RubberDuck' AND
      i.datetime >= '2019-01-01' AND
      i.date_time < '2019-04-01'

Upvotes: 1

Related Questions