Count distinct a combination of two columns

Posted on 2004-11-22
Last Modified: 2008-01-09
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


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
      affiliateID = @afid
      ipAddress != ''
      ipAddress NOT IN (SELECT ipAddress FROM affiliateIPs WHERE afid =  @afid )

GROUP BY prodTypeID, prodID

ORDER BY prodTypeID, prodID



Question by:fevster
    LVL 16

    Accepted Solution

    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

    Author Comment


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


    Author Comment

    Actually sorry this does work, thanks thats great. userID was of type int and ipaddress varchar.


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now