SQL Server – convert date to weekday and count each instance of weekday in second column

Wondering how to select from a table:

FIELDID personID  purchaseID   dateofPurchase
--------------------------------------------------
2         13       147         2013-03-21 00:00:00
3         13       147         2013-03-23 00:00:00
4         13       456         2013-03-24 00:00:00
5         25       123         2013-03-21 00:00:00
6         25       123         2013-03-22 00:00:00
7         25       456         2013-03-21 00:00:00
8         25       456         2013-03-23 00:00:00
9         25       456         2013-03-28 00:00:00
10        25       789         2013-03-21 00:00:00
11        82       147         2013-03-22 00:00:00
12        82       456         2013-03-22 00:00:00

I’d like to get a result table of two columns: weekday and the number of purchases of each weekday.

So far, I have something like this:

SELECT
    CASE DATEPART(WEEKDAY, dateofPurchase)
        WHEN 1 THEN 'Weekend' 
        WHEN 2 THEN 'Monday' 
        WHEN 3 THEN 'Tuesday' 
        WHEN 4 THEN 'Wednesday' 
        WHEN 5 THEN 'Thursday' 
        WHEN 6 THEN 'Friday' 
        WHEN 7 THEN 'Weekend' 
    END AS 'day', 
    COUNT(dateofPurchase) AS 'dates'
FROM 
    dbo.PurchaseRecord
GROUP BY 
    dateofPurchase;

But this returns two Thursday rows and two weekend rows, because they are different weeks. How can I get it to only return 1 row for each Thursday and one for weekend?

Thanks

Answer

I am thinking:

SELECT v.wkday, COUNT(*) as dates
FROM dbo.PurchaseRecord pr CROSS APPLY
     (VALUES (CASE WHEN DATEPART(WEEKDAY, dateofPurchase) IN (1, 7)
                   THEN 'Weekend'
                   ELSE DATENAME(WEEKDAY, dateofPurchase)
              END)
     ) v(wkday)
GROUP BY dateofPurchase
GROUP BY v.wkday;

This uses CROSS APPLY to define wkday once, so it can be used both in the SELECT and GROUP BY. Note that SQL Server has a function that returns the name of the week day. You don’t need a CASE expression for that.

EDIT:

If you want to count unique purchases, then use COUNT(DISTINCT):

COUNT(DISTINCT purchaseId)