0

I am trying the fetch the data of the each user of each month. The table looks like below

----------------------------------------------------------------------------------
| user         |  pm_jan      |       actual_jan    |       pm_feb  | actual_feb |
----------------------------------------------------------------------------------
|   u1         |  w5           |     6               |   0           |    0       |
|   u2         |  w2           |     5               |   0           |    0       |
|   u3         |  w6           |     2               |   w7          |    7       |
|   u4         |   0           |     0               |   w6          |    2       |
|   u5         |   0           |     0               |   w2          |    3       |

In the above table now I want to fetch the total count of pm and actual data of each month until december.

I tried the below but I am able to fetch only the count of jan month but I need it for every month count

    SELECT COUNT(pm_jan),COUNT(actual_jan) 
    FROM pm
    WHERE  pm_jan<>'' AND actual_jan<>''

What is the best query to approach this method

1
  • I'd recommend you to normalize your data into (user, month, pm, actual) structure.
    – Akina
    Commented Jul 19, 2019 at 5:52

2 Answers 2

0
SELECT SUM(pm_jan > 0) count_pm_jan, 
       SUM(actual_jan > 0) count_actual_jan, 
       SUM(pm_feb > 0) count_pm_feb, 
       SUM(actual_feb > 0) count_actual_feb
FROM pm
2
  • I am getting the count of the actual but no the getting the count of pm because it is declared as varchar and contains text as week1 like
    – venkatesh
    Commented Jul 19, 2019 at 7:43
  • @venkatesh Replace > 0 condition with proper non-equiation with proper string literal (emply string or something else - for example, SUM(pm_jan != '')) or proper template checking operator (for example, SUM(pm_jan LIKE "week%")).
    – Akina
    Commented Jul 19, 2019 at 7:46
0

COUNT(x) is the number of rows where x IS NOT NULL.
COUNT(DISTINCT x) is the number of different values of x.
COUNT(*) is the number of rows, and what you should say unless you specifically want of the above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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