SQL Help - statement fix
- or -
Post a project like this2445
£85(approx. $107)
- Posted:
- Proposals: 25
- Remote
- #1657887
- Awarded
15+ years of expertise in development/maintenance ASP.Net, MVC, HTML5/CSS3, AngularJS, C#, SQL, API, WCF, Win Forms
Ahmedabad
Senior Developer / Solution Architect / Database Expert / System Integration specialist
Colombo
1694927153654717828944059617785121777324176673017664431764468175097117425861646092
Description
Experience Level: Expert
General information for the business: I work for a charity!
Description of requirements/functionality: Need help with SQL. I have a SQL Statement that needs attention!
select
rec.emailID,
s.shortname,
count(rec.ID) cnt
from NETCOMMUNITYEMAILJOBRECIPIENT jr
inner join EmailJob_Recipient rec on rec.ID=jr.BBNCMAPID
inner join CONSTITUENTSITE cs on cs.CONSTITUENTID=jr.CONSTITUENTID
inner join SITE s ON s.ID=cs.SITEID
WHERE rec.emailID IN (105974, 100501)
GROUP BY rec.emailID, s.shortname
The above statement is producing this:
emailID shortname cnt
100501 AU 31187
105974 AU 8
100501 DE 1
105974 DE 15
100501 GB 10
105974 GB 199515
100501 HK 4
105974 HK 8
100501 US 7
105974 US 16
And I simple want to only show for EACH EmailID the Country that has the highest Count. So output should look like this:
105974 GB 199515
100501 AU 31187
Only with those proficient with SQL Aggregates should reply! I've picked only two EmailID's to make things simpler for my head.
Thanks!!!
Specific technologies required: SQL 2016
Extra notes:
Description of requirements/functionality: Need help with SQL. I have a SQL Statement that needs attention!
select
rec.emailID,
s.shortname,
count(rec.ID) cnt
from NETCOMMUNITYEMAILJOBRECIPIENT jr
inner join EmailJob_Recipient rec on rec.ID=jr.BBNCMAPID
inner join CONSTITUENTSITE cs on cs.CONSTITUENTID=jr.CONSTITUENTID
inner join SITE s ON s.ID=cs.SITEID
WHERE rec.emailID IN (105974, 100501)
GROUP BY rec.emailID, s.shortname
The above statement is producing this:
emailID shortname cnt
100501 AU 31187
105974 AU 8
100501 DE 1
105974 DE 15
100501 GB 10
105974 GB 199515
100501 HK 4
105974 HK 8
100501 US 7
105974 US 16
And I simple want to only show for EACH EmailID the Country that has the highest Count. So output should look like this:
105974 GB 199515
100501 AU 31187
Only with those proficient with SQL Aggregates should reply! I've picked only two EmailID's to make things simpler for my head.
Thanks!!!
Specific technologies required: SQL 2016
Extra notes:
Reza I.
99% (16)Projects Completed
19
Freelancers worked with
8
Projects awarded
37%
Last project
6 Oct 2022
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies