Using sum with multiple dates but keeping only the latest date

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

Answer

I think you want:

1. Use max to get the latest date.
2. group by sight only, as for all other columns you want aggregated amounts.

SELECT MAX(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 Sight;

Do really need so much casting? If you have used the correct datatypes in your table definition then the following will work:

SELECT MAX([Date])
    , Sight
    , SUM(Blck)
    , SUM(Knock)
    , SUM(Purchased)
    , SUM(Rate)
FROM Prospect
GROUP BY Sight;