SQL - How to count records for each status in one line per day?

Question

I have a table Sales

Sales
--------
id
FormUpdated
TrackingStatus

There are several status e.g. Complete, Incomplete, SaveforLater, ViewRates etc.

I want to have my results in this form for the last 8 days(including today).

Expected Result:

Date Part of FormUpdated, Day of Week, Counts of ViewRates, Counts of Sales(complete), Counts of SaveForLater
--------------------------------------
2015-05-19   Tuesday    3   1   21  
2015-05-18   Monday     12  5   10
2015-05-17   Sunday     6   1   8
2015-05-16   Saturday   5   3   7 
2015-05-15   Friday     67  5   32
2015-05-14   Thursday   17  0   5 
2015-05-13   Wednesday  22  0   9
2015-05-12   Tuesday    19  2   6

Here is my sql query:

select  datename(dw, FormUpdated), count(ID), TrackingStatus 
from Sales
where FormUpdated <= GETDATE()
AND FormUpdated >= GetDate() - 8
group by  datename(dw, FormUpdated), TrackingStatus
order by  datename(dw, FormUpdated) desc

I do not know how to make the next step.

Update

I forgot to mention, I only need the Date part of the FormUpdated, not all parts.

Answer 1

You can use SUM(CASE WHEN TrackingStatus = 'SomeTrackingStatus' THEN 1 ELSE 0 END)) to get the status count for each tracking status in individual column. Something like this. SQL Fiddle

select  
CONVERT(DATE,FormUpdated) FormUpdated,
DATENAME(dw, CONVERT(DATE,FormUpdated)),
SUM(CASE WHEN TrackingStatus = 'ViewRates' THEN 1 ELSE 0 END) c_ViewRates,
SUM(CASE WHEN TrackingStatus = 'Complete' THEN 1 ELSE 0 END) c_Complete,
SUM(CASE WHEN TrackingStatus = 'SaveforLater' THEN 1 ELSE 0 END) c_SaveforLater 
from Sales
where FormUpdated <= GETDATE()
AND FormUpdated >= DATEADD(D,-8,GetDate())
group by  CONVERT(DATE,FormUpdated)
order by  CONVERT(DATE,FormUpdated) desc

Answer 2

You can also use a PIVOT to achieve this result - you'll just need to complete the list of TrackingStatus names in both the SELECT and the FOR, and no GROUP BY required:

WITH DatesOnly AS
(
  SELECT Id, CAST(FormUpdated AS DATE) AS DateOnly, DATENAME(dw, FormUpdated) AS DayOfWeek, TrackingStatus
  FROM Sales
)
SELECT  DateOnly, DayOfWeek, 
        -- List of Pivoted Columns
        [Complete],[Incomplete], [ViewRates], [SaveforLater]
FROM DatesOnly
PIVOT 
(
   COUNT(Id)
   -- List of Pivoted columns
   FOR TrackingStatus IN([Complete],[Incomplete], [ViewRates], [SaveforLater])
) pvt
WHERE  DateOnly <= GETDATE() AND DateOnly >= GetDate() - 8
ORDER BY DateOnly DESC

SqlFiddle

Also, I think your ORDER BY is wrong - it should just be the Date, not day of week.