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.

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

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

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.

```
select accname,serversource,sum (cast([counting] as int) )
from book1
where accname=accname and serversource=serversource
group by accname, serversource
```

And depending on datatype, probably better to cast(sum([counting]) as int)
```
select *,sum ([counting]) over (partition by accname,serversource order by [counting])
from book1
where accname=accname and serversource=serversource
```

Read : https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql
```
select sum (cast([counting] as int) )
from book1
where accname='account1' and serversource='server1'
```

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
```

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.

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

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.

in which case you can use case...

```
sum( case when [counting] > 20 then 1 else 0 end)
```

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 ?

```
sum( case when [counting] > 20 then [counting] else 0 end)
```

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

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

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 trialLet 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
```

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 > 20Iâ€™m just thinking maybe there is another way of doing this ? without a group by or CTE ....

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.

simply we have the query after the data was sump and I can apply the where condition > 20What 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.

Bye, Olaf.

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
```

having the same serversource,IDIncidents and AccName.That needs GROUP BY serversource,IDIncidents,A

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.

Query Syntax

From novice to tech pro — start learning today.

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.