MySQL query to retrieve data based on date (sum of fields)

问题: I'm new to MySQL so please be patient :) I have a table where I would like to retrieve data based on date and do Sum Here is my table client datescanned problem...

问题:

I'm new to MySQL so please be patient :)

I have a table where I would like to retrieve data based on date and do Sum

Here is my table

client    datescanned  problem   title
 abc      2019-02-02    12345a         High      xyz
 abc      2019-02-02    12345b         High      xyz 
 abc      2019-02-02    12345c         High      xyz
 abc      2019-02-02    12345d         Medium    xyz
 abc      2019-02-09    12345e         High      xyz 
 abc      2019-02-09    12345f         High      xyz
 abc      2019-02-09    12345g         Low       xyz
 abc      2019-02-09    12345h         Low       xyz
 abc      2019-02-09    12345j         Low       xyz
 abc      2019-02-16    12345x         High      xyz
 abc      2019-02-16    12345s         High      xyz
 abc      2019-02-16    12345w         High      xyz
 abc      2019-02-16    12345bs        Medium    xyz

My desired output would be

 client   datescanned  problem         High   Medium   Low 
 abc      2019-02-02    12345x          3       1       0
 abc      2019-02-09    12345s          2       0       3
 abc      2019-02-16    12345w          3       1       0 

This is my code

select client,datescanned, problem, severity, 

         count(case when severity = 'High' then 1 end) as High,
         count(case when severity = 'Medium' then 1 end) as Medium,
         count(case when severity = 'Low' then 1 end) as Low

 from ssstest where client = "myuser"
 group by client,datescanned, problem

This will fetch all the numbers correctly, however I want to "sum" up High, Medium, Low per date.

Now I get..

client    datescanned  problem   severity      High  Medium  Low
 abc      2019-02-02    12345a      High        1      0      0
 abc      2019-02-02    12345b      High        1      0      0
 abc      2019-02-02    12345c      High        1      0      0 
 abc      2019-02-02    12345d      Medium      0      1      0 
 abc      2019-02-09    12345e      High        1      0      0 
 abc      2019-02-09    12345f      High        1      0      0 
 abc      2019-02-09    12345g      Low         0      0      1
 abc      2019-02-09    12345h      Low         0      0      1
 abc      2019-02-09    12345j      Low         0      0      1
 abc      2019-02-16    12345x      High        1      0      0
 abc      2019-02-16    12345s      High        1      0      0
 abc      2019-02-16    12345w      High        1      0      0
 abc      2019-02-16    12345bs     Medium      0      1      0 

So basically, I just want to add up High, Medium, Low, group the same date together as one.

 client   datescanned  problem         High   Medium   Low 
 abc      2019-02-02    12345x          3       1       0
 abc      2019-02-09    12345s          2       0       3
 abc      2019-02-16    12345w          3       1       0 

Thanks again for your help!


回答1:

You can try using conditional aggregation

select client,dateadded, computername, severity, 
       count(case when severity='High' then 1 end) as High,
       count(case when severity='Medium' then 1 end) as Medium,
       count(case when severity='Low' then 1 end) as Low,
from mytable where client =%CURRENT_USER_LOGIN%
group by client,dateadded, computername

回答2:

Update(1):

In MySQL/MariaDB query the following doesn't much sense:

=%CURRENT_USER_LOGIN%

If substring you are searching for is CURRENT_USER_LOGIN then use like '%CURRENT_USER_LOGIN%'

select client,dateadded, computername, severity, 
       count(case when severity='High' then 1 end) as High,
       count(case when severity='Medium' then 1 end) as Medium,
-- comma mark ',' before 'from' !!! produces error
       count(case when severity='Low' then 1 end) as Low,
from mytable where client =%CURRENT_USER_LOGIN%
group by client,dateadded, computername
  • 发表于 2019-02-22 05:24
  • 阅读 ( 180 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除