MYSQL query where I want to filter one specific column

Question

I'm trying to filter, so the column salaryMonth only contains data which has 2020 inside, so 2019 is filtering out.

SELECT sum(km_amount) as total
     , user_id
     , salaryMonth 
  from kms
     , users 
 where users.id = kms.user_id 
 group 
    by salaryMonth
     , user_id

where `YEAR(salaryMonth) = YEAR(NOW())` this is for current year, and if you want to define which year then its like `YEAR(salaryMonth) = 2018` — Zeljka, Mar 05 '20 at 08:31

score 0 · Accepted Answer · edited Mar 05 '20 at 08:32

0

Did you try something like this?

SELECT
    sum(km_amount) as total, 
    user_id, 
    salaryMonth 
FROM kms, users 
WHERE
    users.id=kms.user_id 
    AND salaryMonth LIKE '%2020%' 
GROUP BY 
    salaryMonth, user_id

edited Mar 05 '20 at 08:32

Moseleyi

2,585
1
24
46

answered Mar 05 '20 at 08:30

DmitriyH

126
7

2

Note that we stopped writing queries this way ca 1992 – Strawberry Mar 05 '20 at 08:33
1

@Strawberry could you clarify what is wrong with this way? – DmitriyH Mar 05 '20 at 09:27
https://stackoverflow.com/questions/8581774/can-someone-help-explain-why-not-using-a-sql-join-is-bad-practice-and-wrong/8582168#8582168 – Strawberry Mar 05 '20 at 09:59
2

I mean, why post a snarky comment and not just be helpful in the first place? – Karl Lorey Mar 05 '20 at 10:19
1

@DmitriyH . . . To be specific, it is rather shocking that anyone would provide an answer using commas in the `FROM` clause rather than proper, explicit, **standard**, readable `JOIN` syntax. – Gordon Linoff Mar 05 '20 at 12:22
yes, thanks for the clarification. My first thought was that 'like' using is a wrong way. – DmitriyH Mar 05 '20 at 13:11

score 0 · Answer 2 · answered Mar 05 '20 at 10:15

You could save yourself no end of misery be refactoring your table as:

total user_id salary_yearmonth
  625      64 2020-02-01 
  595      70 2020-02-01 
  600      74 2020-02-01
  632      75 2020-02-01
  471      77 2020-02-01
  788      29 2019-03-01
   35       4 2020-03-01
   22      39 2020-03-01
  373      47 2020-03-01
  196      53 2020-03-01
  140      74 2020-03-01
  228      75 2020-03-01
   49      29 2019-04-01 
   96      63 2019-05-01 
  406       4 2019-06-01 
  966       4 2019-07-01 
  514       1 2019-08-01 
  637       4 2019-08-01
  580      47 2019-08-01
   11       1 2019-09-01

Actually the salaryMonth column is a string but in the same table I have a date field for same purpose, so I could filter it from there as well. Your right :) — Sajid Latif, Mar 06 '20 at 06:56

MYSQL query where I want to filter one specific column

2 Answers2