Using sum with multiple dates but keeping only the latest date

Question

0.00/5 (No votes)

See more:

I have table called Prospect that looks like this and I'm trying to SUM the value so that I have only 1 GOERGIA, ALABAMA, AND TEXAS and the date kept in the date field is the latest date.

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-02-16       TEXAS        2      0      0           0.007
2020-02-16       TEXAS        2      0      0           0.007
2020-02-17       TEXAS        1      0      0           0.005

I would like to have a table 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-02-17   TEXAS    5      0       0           0.019

What I have tried:

SQL

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 ]

Posted 13-May-20 11:16am

learning_new

Updated 14-May-20 2:14am

Richard Deeming

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2020-05-13T13:39:00

You are actually going to need 3 queries:
Query 1 is going to aggregate the sums for each state.
Query 2 is going to get the state and last date
Query 3 will join the above queries.

Here is the table structure and some of your data withing it.

SQL

DECLARE @Data TABLE (
   dDate     DATE,
   dSite     VARCHAR(32),
   Blck      INT,
   Knock     INT,
   Purchased DECIMAL(5,2),
   Rate      DECIMAL(5,4)
)
INSERT @data VALUES
  ('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)

Now for the first query; getting the sum for each site

SQL

SELECT dSite
     , sumBlock = SUM(Blck)
     , sumKnock = SUM(Knock)
     , sumPurch = SUM(Purchased)
     , sumRate  = SUM(Rate)
FROM @Data
GROUP BY dSite

And the second query for Sites and the latest date

SQL

SELECT dSite
     , maxDate = Max(dDate)
FROM @Data
GROUP BY dSite

And for the final query... we are going to join these by using them as subqueries

SQL

SELECT b.maxDate, a.*
FROM (
   SELECT dSite
        , sumBlock  = SUM(Blck)
        , sumKnock  = SUM(Knock)
        , sumPurch  = SUM(Purchased)
        , sumRate   = SUM(Rate)
   FROM  @Data
   GROUP BY   dSite               ) AS a

INNER JOIN (
   SELECT dSite
        , maxDate   = Max(dDate)
   FROM  @Data
   GROUP BY   dSite               ) AS b ON a.dSite = b.dSite

ORDER BY b.maxDate, a.dSite

Richard Deeming · Accepted Answer · 2020-05-14T02:15:00

Solution 2

You only need one query, but you need to use MAX on the date column, and only GROUP BY the state:

SQL

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

Posted 14-May-20 2:15am

Richard Deeming

v2

Comments

Maciej Los 14-May-20 9:40am

5ed!