Sum values based on column matching

Guys,
I have a table that I need to  perform sum operation, the sum values is taking from   the [Counting] column, however  before  sum, the value must be validated against column [serversource] and [accname]. I tried with following query for test but it doesn't fit the result that I'm looking for


  select *,sum (cast([counting] as int) )
  from book1
  where accname=accname and serversource=serversource
motioneyeAsked:
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.

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Can you please provide some sample data and expected output?
Geert GOracle dbaCommented:
have you got a sample of data
and a sample of what the result should look like ?
Mark WillsTopic AdvisorCommented:
To aggregate you need to either GROUP BY, or use Windowed Functions. Better to do
  select accname,serversource,sum (cast([counting] as int) )
  from book1
  where accname=accname and serversource=serversource
group by accname, serversource

Open in new window

And depending on datatype, probably better to cast(sum([counting]) as int)
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Geert GOracle dbaCommented:
this line doesn't do anything
  where accname=accname and serversource=serversource

Open in new window

Mark WillsTopic AdvisorCommented:
Or using Windowed functions (depending on SQL Server Version)
select *,sum ([counting]) over (partition by accname,serversource order by [counting])
  from book1
  where accname=accname and serversource=serversource

Open in new window

Read : https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql
Olaf DoschkeSoftware DeveloperCommented:
If you only want one sum for a specific account and server you can use your query, just put in the concrete values:

select sum (cast([counting] as int) )
  from book1
  where accname='account1' and serversource='server1'

Open in new window


If you want an overview of sums of all existing account and server combinations, don't filter with WHERE, simply GROUP BY, and include the accname, serversource columns to see for which values that is the sum:
select accname, serversource, sum (cast([counting] as int) )
  from book1
  group by accname, serversource

Open in new window


What you can't do is Select *, sum(), especially the * part. Even without GROUP BY a SUM means a sum of the values of many rows, you can't have other columns single row values in the result, your result only has one row as using SUM without GROUP BY means all data is considered as one single group. For other columns data to appear, you have to either SUM() other columns too, or pick MIN()/MAX() or use whatever other so-called aggregation function. What you can put beside a sum is something, that's constant for all rows the sum is coming from. By definition that's the columns you group by in a GROUP BY query.

If you indicate you want all data and a running sum, that needs window functions, Mark Wills gave that example.

Bye, Olaf.
motioneyeAuthor Commented:
Hi,
Sorry to left the attachment from my initial post.   the result is look fine with this query below, but when I includes the where clause I get inaccurate data . After the sum the criteria of the record is  only select those record with  [counting]>=20, so how do we sum only those record and then only produce a results ?
any other way around we can group the data instead of using group by ? group by will force me to include all the selection column in the  group by clause.

 select accname,serversource,sum (0+[counting]) as [BreachCount]
  from book1
  where [counting]>=20
  group by accname,serversource
Book1.xlsx
Olaf DoschkeSoftware DeveloperCommented:
> group by will force me to include all the selection column in the  group by clause

Well, no, it's vice versa, you can't have the other columns in the result, if you add more, you change how many rows a group spans and if you add all you may make every single row its own group, and that's not what you want.

Just stop thinking the Excel SUM() function way, your query will only give you the total line or lines (depending on number of groups) or even just total cells , not all individual rows before the total, too. If you want that, export your data to excel and add a SUM() to a cell to get the total for that column. Especially if you expect the totals to update, when you edit data, That's not what the SQL result will do, it'll just reflect the result at the time of query.

What's not working with where [counting]>=20? If this doesn't error, then did you need the CAST(counting as int)  you did earlier, at all? Or is that now missing for the where clause to work?

Bye, Olaf.
Mark WillsTopic AdvisorCommented:
it almost sounds as if you want to sum() the number of counting > 20

in which case you can use case...
sum( case when [counting] > 20 then 1 else 0 end)

Open in new window


But when using aggregates, you need to group by (assuming other columns are involved) or use windowed functions. Or could use a CTE to grab the data first, or select from a subquery....

Why not try to describe what you are looking to do as simply as you can. Showing us code and saying "it doesnt do what I want" is a bit confusing.

So, what columns do we need to include - with some sample raw data, and using that raw data, what do you want as a result ?

Does that make sense ?
Olaf DoschkeSoftware DeveloperCommented:
rather
sum( case when [counting] > 20 then [counting] else 0 end)

Open in new window

But then that would work same as
sum([counting]) 
...
WHERE [counting]>20

Open in new window

Unless you want to count instead of summing, then use COUNT and not SUM:
COUNT(*)
...
WHERE [counting]>20

Open in new window


It would really help if you don't only post book1.xlsx but also what result you expect from the query.

Bye, Olaf.

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
motioneyeAuthor Commented:
Hi,
Let me try this way to explains.

In the book1 table I need to sum the column [counting], whenever it meet the requirement to produce the result which is having the same serversource,IDIncidents and AccName. So the query below will do
select accname,serversource,sum (0+[counting]) as [BreachCount]
  from book1 
  group by accname,serversource

Open in new window

I'm aware that I can split the query and grab those value after sum  in CTE   then join this CTE back to the original query, this one of way getting this done,  simply we have the query after the   data was sump and I can  apply the where condition > 20

I’m just thinking maybe there is another way of doing this ? without a group by or  CTE ....
Mark WillsTopic AdvisorCommented:
>> But then that would work same as ...

Depends on what you really want to count. Number of incidents exceeding an "acceptable" threshold then you want to count the number of times it happened. Happened twice (according to spreadsheet) versus 123 which could mean an average of 6 violations, whereas it was only 2.
Olaf DoschkeSoftware DeveloperCommented:
simply we have the query after the   data was sump and I can  apply the where condition > 20
What do you mean with that, you want results HAVING  SUM([counting])>20?

Then you don't apply WHERE, but HAVING  SUM([counting])>20

Bye, Olaf.
Olaf DoschkeSoftware DeveloperCommented:
Mark, I don't know why you suddenly talk about counts, when the overall thread was always talking of summing. Even if the field summed is called [counting], the wanted arithmetic on it always was SUM of it's values. The condition is just exclusing low values from the sum, that doesn't turn summing to counting, necessarily.

Bye, Olaf.
Mark WillsTopic AdvisorCommented:
@motioneye,
Again, I ask, dont show us some code, show some raw numbers / columns, and then based on that raw data, what you are looking for as a result.

We are largely guessing the requirement.... Here is another guess
select accname,serversource,sum( case when [counting] > 20 then 1 else 0 end) as [BreachCount], sum([counting]) as TotalBreaches
  from book1 
  group by accname,serversource

Open in new window

Olaf DoschkeSoftware DeveloperCommented:
having the same serversource,IDIncidents and AccName.
That needs GROUP BY serversource,IDIncidents,AccName, that's pretty straight forward.
In case of your sample input data IDIncidents is the same value for all data, which makes that unimportant.

We don't have seen yet,
a) what result you get from which query you took, there's many guesswork and many options and we don't even know what you picked up.
b) what values you deem wrong in the results and what you expect instead

That would help us, to see where your misunderstanding about what a query does is. It's already quite obvious you have some misunderstanding if none of the queries gives you an expected result.

Bye, Olaf.
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.