How to produce a distinct count of records that are stored by day by month

I have a table with several "ticket" records in it. Each ticket is stored by day (i.e. 2011-07-30 00:00:00.000) I would like to count the unique records in each month by year I have used the following sql statement

SELECT DISTINCT
   YEAR(TICKETDATE) as TICKETYEAR,
   MONTH(TICKETDATE) AS TICKETMONTH,
   COUNT(DISTINCT TICKETID) AS DAILYTICKET开发者_运维技巧COUNT
FROM
   NAT_JOBLINE
GROUP BY
   YEAR(TICKETDATE),
   MONTH(TICKETDATE)
ORDER BY
   YEAR(TICKETDATE),
   MONTH(TICKETDATE)

This does produce a count but it is wrong as it picks up the unique tickets for every day. I just want a unique count by month.

---

Try combining Year and Month into one field, and grouping on that new field. You may have to cast them to varchar to ensure that they don't simply get added together. Or.. you could multiple through the year...

SELECT    
   (YEAR(TICKETDATE) * 100) + MONTH(TICKETDATE),
   count(*) AS DAILYTICKETCOUNT
FROM    NAT_JOBLINE GROUP BY
 (YEAR(TICKETDATE) * 100) + MONTH(TICKETDATE)

---

Presuming that TICKETID is not a primary or unique key, but does appear multiple times in table NAT_JOBLINE, that query should work. If it is unique (does not occur in more than 1 row per value), you will need to select on a different column, one that uniquely identifies the "entity" that you want to count, if not each occurance/instance/reference of that entity.

(As ever, it is hard to tell without working with the actual data.)

---

I think you need to remove the first distinct. You already have the group by. If I was the first Distict I would be confused as to what I was supposed to do.

    SELECT 
      YEAR(TICKETDATE) as TICKETYEAR,
      MONTH(TICKETDATE) AS TICKETMONTH,
      COUNT(DISTINCT TICKETID) AS DAILYTICKETCOUNT
    FROM NAT_JOBLINE
    GROUP BY YEAR(TICKETDATE), MONTH(TICKETDATE) 
    ORDER BY YEAR(TICKETDATE), MONTH(TICKETDATE)

---

From what I understand from your comments to Phillip Kelley's solution:

SELECT TICKETDATE, COUNT(*) AS DAILYTICKETCOUNT
FROM  NAT_JOBLINE
GROUP BY TICKETDATE

should do the trick, but I suggest you update your question.