SUM of records returned

Hi,

I have the following SQL code which returns the relevant records.  Is it possible to have the code just return the SUM of all the FrontsCaptured and BacksCaptured fields?

SELECT dbo_ScanBatches.CreatedUser, dbo_ScanBatches.FrontsCaptured, dbo_ScanBatches.BacksCaptured, dbo_ScanBatches.CreatedDate, Month([CreatedDate]) AS ScanMonth
FROM dbo_ScanBatches
GROUP BY dbo_ScanBatches.CreatedUser, dbo_ScanBatches.FrontsCaptured, dbo_ScanBatches.BacksCaptured, dbo_ScanBatches.CreatedDate, Month([CreatedDate])
HAVING (((Month([CreatedDate]))=Month(Now())));

Open in new window


Thanks,
Anthony
LVL 1
anthonytrAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
The above code seems to return the complete sum of all records and doesn't apply the CROSS APPLY section of code.

That makes sense really, since I'm using a column in the table to compare to.  I misunderstood, and thought you were trying to break data for the month (year, whatever) into weeks based on CreateDate.

If instead you want the most recent calendar week, for example, than you don't need the CROSS APPLY you can just use GETDATE():

SELECT SUM(SB.FrontsCaptured) AS FrontsCaptured, SUM(SB.BacksCaptured) AS BacksCaptured
FROM ScanBatches SB
WHERE SB.[CreatedDate] >= DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 7, CAST(GETDATE() AS date)) AND
    SB.[CreatedDate] < DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 7 + 7, CAST(GETDATE() AS date))
0
 
OMC2000Commented:
If you want sum of unique values

SELECT  SUM(dbo_ScanBatches.FrontsCaptured), SUM(dbo_ScanBatches.BacksCaptured) from 
(SELECT dbo_ScanBatches.CreatedUser, dbo_ScanBatches.FrontsCaptured, dbo_ScanBatches.BacksCaptured, dbo_ScanBatches.CreatedDate, Month([CreatedDate]) AS ScanMonth
FROM dbo_ScanBatches
GROUP BY dbo_ScanBatches.CreatedUser, dbo_ScanBatches.FrontsCaptured, dbo_ScanBatches.BacksCaptured, dbo_ScanBatches.CreatedDate, Month([CreatedDate])
HAVING (((Month([CreatedDate]))=Month(Now())))) A;

Open in new window


If you want sum of all values

SELECT  SUM(dbo_ScanBatches.FrontsCaptured), SUM(dbo_ScanBatches.BacksCaptured) 
FROM dbo_ScanBatches
GROUP BY dbo_ScanBatches.CreatedUser, dbo_ScanBatches.CreatedDate, Month([CreatedDate])
HAVING (((Month([CreatedDate]))=Month(Now())));

Open in new window

0
 
SujithData ArchitectCommented:
Are you looking at retaining the current grouping? -

SELECT dbo_ScanBatches.CreatedUser, dbo_ScanBatches.FrontsCaptured, dbo_ScanBatches.BacksCaptured, dbo_ScanBatches.CreatedDate, Month([CreatedDate]) AS ScanMonth
        ,SUM(dbo_ScanBatches.FrontsCaptured) as sumfronts, SUM(dbo_ScanBatches.BacksCaptured) sumbacks
FROM dbo_ScanBatches
GROUP BY dbo_ScanBatches.CreatedUser, dbo_ScanBatches.FrontsCaptured, dbo_ScanBatches.BacksCaptured, dbo_ScanBatches.CreatedDate, Month([CreatedDate])
HAVING (((Month([CreatedDate]))=Month(Now())));

if you just want the net sums -

SELECT SUM(dbo_ScanBatches.FrontsCaptured) as sumfronts, SUM(dbo_ScanBatches.BacksCaptured) sumbacks
FROM dbo_ScanBatches
where (((Month([CreatedDate]))=Month(Now())));
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
anthonytrAuthor Commented:
That is all great!  Thanks.

One more thing (sorry).  I have Month(Now().  Is it possible to filter by WEEK instead of current month?

Anthony
0
 
OMC2000Commented:
You could use

DatePart(week, [CreatedDate])= DatePart(week, Now())
0
 
Scott PletcherSenior DBACommented:
You should use WHERE not HAVING to check for month.  You're forcing totals for all months to be computed, then ignoring everything but the current month.

SELECT SUM(SB.FrontsCaptured) AS FrontsCaptured, SUM(SB.BacksCaptured) AS BacksCaptured
FROM dbo_ScanBatches SB
WHERE ((Month(SB.[CreatedDate]))=Month(Now()))

As to by WEEK, how do you define a week?  From Sun thru Sat, Mon thru Sun, what?
0
 
anthonytrAuthor Commented:
Our week is Mon - Sunday
0
 
Scott PletcherSenior DBACommented:
This will list the most recent week, starting on Monday, i.e, the current week.

If you need the previous week, subtract 7 days from the WeekStartDate calc.

SELECT SUM(SB.FrontsCaptured) AS FrontsCaptured, SUM(SB.BacksCaptured) AS BacksCaptured
FROM dbo_ScanBatches SB
CROSS APPLY (
    /* back up the current date to most recent Monday (if today is Monday, then today) */
    SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, SB.[CreatedDate]) % 7, SB.[CreatedDate]) AS WeekStartDate
) AS alias1
WHERE SB.[CreatedDate] >= WeekStartDate AND SB.[CreatedDate] < DATEADD(DAY, 7, WeekStartDate)
1
 
Mark WillsTopic AdvisorCommented:
You dont need any other columns if you only want to SUM.

But then it is up to your WHERE clause to limit the range / filter the dataset.

SELECT SUM(dbo_ScanBatches.FrontsCaptured) as sumfronts, SUM(dbo_ScanBatches.BacksCaptured) as sumbacks
FROM dbo_ScanBatches
where (((Month([CreatedDate]))=Month(Now())));


NOW() is not sql server but it is MySQL Server

SQL Server uses GETDATE()  ( and dateadd  datepart are SQL Sever, not MySQL)

So, you could use the same DATEPARTS to compare between createdDate and getdate() - if SQL Server.

Or, we can add MySQL to your topic lists :)

In MySQL use WEEK(date,1) where 1 is the mode (ie Monday week commencing)    so still use createddate and now() just wrapped inside WEEK(createddate,1) = WEEK(now(),1)

See : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_week
0
 
PortletPaulfreelancerCommented:
Which database is this for?

NB:
You should be using the where clause (not the having clause) regardless of database type
Avoid applying a function on the  CreatedDate instead calculate a from date and a to date then filter that way e.g.

Where CreatedDate >= @from_dt and CreatedDate < @to_dt

But the sql to use for calculating the dates will differ by database
(+edit)
E.g. for SQL Server: Scott Pletcher's use of cross apply seen above is a way to calculate the wanted dates for a week, and this leaves the column CreatedDate untouched by any function.
0
 
Mark WillsTopic AdvisorCommented:
@Paul

Since when did SQL Server introduce NOW() as a function ?

The use of NOW() has been apparent from the get go - and everyone has coded for it...

So, I added MySQL Server as a TA.

And if everyone was happy enough to code  Month(SB.[CreatedDate])=Month(Now()) - an acknowledgement that is isnt SQL Server....

Then to get Week from a Monday week Commencing use WEEK(SB.[CreatedDate],1)=WEEK(Now(),1)
0
 
PortletPaulfreelancerCommented:
@Mark

I'm aware that now() isn't T-SQL but does the person want an equivalent fo T-SQL? i don't know. I also didn't know it was you who added the topic.

But now I'm just more confused because it can't be for both,  which is why I  asked the question "Which database is this for?".
0
 
anthonytrAuthor Commented:
It is MSSQL - i don't believe I added MySQL to the topics.
0
 
anthonytrAuthor Commented:
@Scott

USE [BatchStatus]

SELECT SUM(SB.FrontsCaptured) AS FrontsCaptured, SUM(SB.BacksCaptured) AS BacksCaptured
FROM ScanBatches SB
CROSS APPLY (
    /* back up the current date to most recent Monday (if today is Monday, then today) */
    SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, SB.[CreatedDate]) % 7, SB.[CreatedDate]) AS WeekStartDate
) AS alias1
WHERE SB.[CreatedDate] >= WeekStartDate AND SB.[CreatedDate] < DATEADD(DAY, 7, WeekStartDate)

Open in new window


The above code seems to return the complete sum of all records and doesn't apply the CROSS APPLY section of code.  Any tips?
Anthony
0
 
PortletPaulfreelancerCommented:
Try using CROSS JOIN instead
0
 
Mark WillsTopic AdvisorCommented:
@anthony,

I added MySQL to the topics. And will remove it.

I dont know how it is running on MSSQL because there is no function NOW()

In MSSQL Server parlance it is GETDATE()

If I do SELECT MONTH(NOW()) I will get the error :

msg 195, Level 15, State 10,  ... 'NOW' is not a recognized built-in function name.

So, for your filtering the query, I would have expected

WHERE Month([CreatedDate])=Month(Getdate())

And if wanting Week in SQL Server, could do

WHERE datepart(ww,[CreatedDate])=datepart(ww,Getdate())

and it will compare like for like - assuming of course you have some kind of date range to  also include in your selection.

A little bit different if you want "current week" whereby the definition of Week Commencing then has a very important part to play

A couple of things to consider....

1) since sql server 2008 you can get ISO_WEEK which is a Monday week commencing : datepart(iso_week,getdate())
2) if you are Monday week commencing, would be worthwhile checking the result to see if your system has datefirst set to 1: SELECT @@DATEFIRST  


So, if wanting week commencing Monday for current week, simply use iso_week datepart ie


SELECT SUM(dbo_ScanBatches.FrontsCaptured) as sumfronts, SUM(dbo_ScanBatches.BacksCaptured) as sumbacks
FROM dbo_ScanBatches
WHERE datepart(iso_week,[CreatedDate])=datepart(iso_week,Getdate())


Either way, I would be inclined to include a date range as the first part of your WHERE clause to limit the range....

Cheers,
Mark Wills
0
 
anthonytrAuthor Commented:
@Scott

That did the trick!  Thanks
0
 
Scott PletcherSenior DBACommented:
You're welcome.  [Btw, the code I used is independent of any/all date settings.  So DATEFIRST could be 7 or 1 or anything and the code will still work accurately.]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.