Link to home
Start Free TrialLog in
Avatar of anthonytr
anthonytrFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of OMC2000
OMC2000
Flag of Russian Federation image

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

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())));
Avatar of anthonytr

ASKER

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
You could use

DatePart(week, [CreatedDate])= DatePart(week, Now())
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?
Our week is Mon - Sunday
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)
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
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.
@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)
@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?".
It is MSSQL - i don't believe I added MySQL to the topics.
@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
Try using CROSS JOIN instead
@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
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Scott

That did the trick!  Thanks
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.]