I have a table called Prospect that looks like this and I'm trying to SUM the value so that I have only 1 column for GEORGIA, ALABAMA,TEXAS and the date kept in the date field be the latest date entry.
Date Sight Blck Knock Purchased Rate
2020-02-13 GEORGIA 11 6 54.55 0.0385
2020-02-13 GEORGIA 1 1 100 0.0035
2020-02-14 GEORGIA 2 0 0 0.007
2020-02-12 ALABAMA 2 0 0 0.007
2020-02-15 ALABAMA 2 0 0 0.007
2020-02-16 ALABAMA 2 1 50 0.007
2020-04-08 TEXAS 2 0 0 0.007
2020-04-18 TEXAS 2 0 0 0.007
2020-05-10 TEXAS 1 0 0 0.005
I would like to have a resultset that looks like this
Date Sight Blck Knock Purchased Rate
2020-02-14 GEORGIA 14 7 154.55 0.049
2020-02-16 ALABAMA 6 1 50 0.021
2020-05-10 TEXAS 5 0 0 0.019
This is what I've tried, but it is not summing properly:
SELECT cast (Date as date)
, Sight
, SUM(CAST(Blck AS INT))
, SUM(CAST(Knock AS INT))
, SUM(CAST(Purchased AS money))
, SUM(CAST(Rate AS money))
FROM Prospect
GROUP BY cast(Date as Date)
, [Blck]
, [Knock]
, [Purchased]
, [Rate]
Thanks for any feedback
Sight
in thegroup by
clause.