MySQL Union statement and Count

Question

I'm trying to count the number of occurrences for the same title.

My Code

SELECT 

client AS Client,
datescanned as "Date Scanned",
scanner AS Scanner,
risk AS Risk, 
host AS Host, 
name AS Title, 
solution AS Solution
FROM sss1webapp_latest

WHERE risk regexp "Critical|High" 
AND client = "myself" 
group by Title;

UNION

SELECT 


client AS Client,
datescanned as "Date Scanned",
scanner AS Scanner,
AttackScore AS Risk, 
WebSite AS Host, 
AttackType AS Title, 
Recommendation AS Solution
FROM sss2webapp_latest
WHERE AttackScore regexp "5-Critical|4-High" 
AND client = "myself"

My Table (just a representation, didn't added all fields...)

client  datescanned  title  ....
myself  2019-03-11  Backported Security Patch 
myself  2019-03-11  Backported Security Patch
myself  2019-03-11  Backported Security Patch Detection (SSH)
myself  2019-03-11  Backported Security Patch Detection (SSH)
myself  2019-03-11  Backported Security Patch Detection (SSH)
myself  2019-03-11  Backported Security Patch Detection (SSH)
myself  2019-03-11  SSL Version Issues

My desired output

count client datescanned title
2     myself  2019-03-11    Backported Security Patch 
4     myself  2019-03-11    Backported Security Patch Detection (SSH)
1     myself  2019-03-11    SSL Version Issues

(adding count and group same title and counting the # of occurrences.

Tried adding count(0)as tcount, after the select statement but error.

Feedback welcome! Thx! Nathalie

You have an extra semi-colon on your first select statement(`group by Title;#<- get rid of this`), I think you'll need to do something like this: https://stackoverflow.com/questions/11731655/how-to-do-a-count-on-a-union-query — Michael Peng, Apr 11 '19 at 04:16

Riad · Answer 1 · 2019-04-11T04:57:50.843

0

 select count(*) as count, client, datescanned, title, risk AS Risk  
 from sss1webapp_latest 
 where risk regexp "Critical|High" AND client = "myself"  group by title 

 union

 select count(*) as count, client, datescanned, title, AttackScore AS Risk 
 from sss2webapp_latest 
 where AttackScore regexp "5-Critical|4-High" AND client = "myself" group by title

you may select other columns but column numbers should be same and the name also for union

edited Apr 11 '19 at 04:57

answered Apr 11 '19 at 04:45

Riad

3,822
5
28
39

Tried that.. if I add count(*) as count in one statement without UNION, it's fine. However, as soon I add several count(*), i'm getting "cannot calculate position of (0) within UNION SELECT count(0) AS count; client AS Client, datescanned as "Date Scanned........" – enkiki Apr 11 '19 at 05:31

Michael Peng · Answer 2 · 2019-04-11T07:10:00.317

I think I was able to achieve the desired result using a combination of count(*) and sum(*). You probably should be using as a UNION ALL instead of a UNION as well, as we want duplicate records to be counted. Check out this db fiddle: https://www.db-fiddle.com/f/uVbD2izXHGPjCkkyk1GHRn/2

You might have to tweak the query a bit by adding in the extra columns and the where clause.

I pulled together this idea from another answer: https://stackoverflow.com/a/32377265/9842191

MySQL Union statement and Count

2 Answers2