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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
@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
PortletPaulEE Topic AdvisorCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.