How to pull count group by one of two fields from database?

Transact-SQL
1

I am using a database table _CLICK and joining with another table _JOB to extract a list of all clicked URLs from an email along with the count (how many unique subscribers clicked on the link) for specific emails (aka job ids).

Can anyone help me with the approach? This is what I got so far

SELECT c.JobID, j.EmailName, c.LinkName, c.LinkContent, c.URL, c.IsUnique, count(*) as Total_Count
FROM _Click c
INNER JOIN _Job j ON j.JobID = c.JobID
where c.JobID IN ('123456','111111')
and c.IsUnique = 'true'
GROUP BY c.jobid, c.URL

Basically I am looking for an output like this:

JobID URL LinkName Total_Count
1 www.google.com CTA_1 10
1 www.yahoo.com CTA_2 4
2 www.hotmail.com link_top 50
2 www.spacex.com footer_link 10

It would be great to include email name in the output (if possible).

2

The email part is easy to fix, just use MAX().

The other depends. Is the LinkName always the same for a given URL (wouldn't think so, but hey, not my data, so who knows)? If so, you can use MAX(LinkName) just like for email.

If it's different, you either need to include it in the GROUP BY or just get a unique count or some other aggregate value for the column too. You could do the MIN() or MAX() or, if on a recent enough version of SQL Server, FIRST_VALUE() or LAST_VALUE().

SELECT c.JobID, MAX(j.EmailName) AS EmailName, c.LinkName, c.URL, c.IsUnique, count(*) as Total_Count
FROM _Click c
INNER JOIN _Job j ON j.JobID = c.JobID
where c.JobID IN ('123456','111111')
and c.IsUnique = 'true'
GROUP BY c.jobid, c.URL, c.LinkName
3

Hi @ScottPletcher thank you for you response and advise.

For my understanding, what exactly max or min functions are doing in this case when the values in those fields is text like email name, linkname etc?

Is it possible to get results sorted in desc by count(*) as Total_Count?

Also - link name will be different for each link/url.

4

Update: Based on the direction provided by @ScottPletcher I ended up creating the query that is going what I need. Thank you @ScottPletcher

SELECT TOP 1000 c.JobID, MAX(j.EmailName) AS EmailName, c.LinkName, c.URL, c.IsUnique, count(distinct c.SubscriberID) as Total_Count
FROM _Click c WITH (NOLOCK)
INNER JOIN _Job j WITH (NOLOCK) ON j.JobID = c.JobID
where c.JobID IN ('123456','111111')
and c.IsUnique = 'true'
GROUP BY c.jobid, c.URL, c.LinkName, c.IsUnique
ORDER BY c.jobid, count(distinct c.SubscriberID) desc