Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1929
  • Last Modified:

Count distinct a combination of two columns

Hi all,

I am trying to produce a report giving a number of unique clicks using the userID and ipAddress column of a table. i.e I only want to count unique values of userid and ip address.

I cant use count(disctinct userID, ipAddress) as syntax error. My sp is below:

Can anyone help?

CREATE PROCEDURE usp_afapply_periodReport
(
      @afid            varchar(20),
      @dateFrom       datetime,
      @dateTo      datetime
)

AS

SELECT prodTypeID As [Product Type], prodID As [ProductID], COUNT (prodID) As [Total Applys],  COUNT(DISTINCT userID) As [Unique Applys]

FROM applyTracking

WHERE hitDate >= @dateFrom AND hitDate <= @dateTo
            AND
      affiliateID = @afid
            AND
      ipAddress != '80.176.65.178'
            AND
      ipAddress NOT IN (SELECT ipAddress FROM affiliateIPs WHERE afid =  @afid )

GROUP BY prodTypeID, prodID

ORDER BY prodTypeID, prodID

GO

Thanks

fevster
0
fevster
Asked:
fevster
  • 2
1 Solution
 
muzzy2003Commented:
Would this work with your data?

SELECT prodTypeID AS [Product Type], prodID AS [ProductID], COUNT(prodID) AS [Total Applys],
COUNT(DISTINCT CAST(userID AS char(10)) + ipAddress) AS [Unique Applys]

FROM applyTracking
0
 
fevsterAuthor Commented:
Hi,

Ive tried that and it brings back no results, so it isnt working.

fevster
0
 
fevsterAuthor Commented:
Actually sorry this does work, thanks thats great. userID was of type int and ipaddress varchar.

fevster
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now