Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Multiple sum's of the same column based on another column in the one query

Posted on 2010-11-09
25
431 Views
Last Modified: 2012-05-10
here is the layout in a basic form:

name   code   amount


I need to write a single select statement that will add all the amounts of each distinct code and also have it grouped by name.

For example, if one Name had 2 distinct codes but in 10 entries, I would want my results to be 2 rows, with the sum of each codes amounts.




 
0
Comment
Question by:RustyZ32
  • 10
  • 6
  • 4
  • +2
25 Comments
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34093118
Hi,

Try this...

SELECT code, name, sum(amount)
FROM yourTable
GROUP BY code, name


Cheers
0
 
LVL 14

Expert Comment

by:leoahmad
ID: 34093138
select name, sum(amount) from table group by code,name
0
 
LVL 6

Author Comment

by:RustyZ32
ID: 34093171
I'm sorry. I  was not clear in my question.

The main challenge is that several of the values in the code column need to stay unsummarized.

i need a way to manually specify which values are to be summed and which are to be left alone.


0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 14

Expert Comment

by:leoahmad
ID: 34093184
can you post some sample data and required results
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34093202
Is it going to be more than one at a time?

If not, you can use a parameter

SELECT code, name,
sum(
CASE WHEN @parameter = 1 then amount
when @parameter = 2 then anotherField
END
)
FROM yourTable
GROUP BY code, name


If not, give more info...


Cheers
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 34093321

Is there any limitations to your code that you could not use an ORDER clause? See the following:

SELECT Name, Code, sum(Amount) FROM table GROUP BY Name, Code
ORDER BY Name, Code
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34093366
Hi,

There's no incompatibility between GROUP BY and ORDER BY, so you can use them both.

'Group by' sort the results somehow, but if you need any specific order, you can use ORDER BY.


Cheers.
0
 
LVL 6

Author Comment

by:RustyZ32
ID: 34093446
yes, it will be more than one at a time. I might need 5 of the codes summed and 3 not summed.

can I do some kind of series of nested statements with individual WHERE clauses for the different codes?
0
 
LVL 18

Expert Comment

by:deighton
ID: 34093459
select name,code,sum(amount)
FROM yourTable
WHERE code IN (SELECT TableHoldingCodesToSum.CODE FROM TableHoldingCodesToSum)
GROUP BY code, name
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34093469
HI,

can you use dynamic SQL? you can build the query according a parameter (or some) ...

anyway, can you post the field and an example of what is your expected results?


cheers.
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34093506
Sorry, I think I got it...

you say that some codes need to be summarize and some not?

SELECT name,code,sum(amount)
FROM yourTable
WHERE code IN (CODES TO SUMMARIZE)
GROUP BY code, name
UNION

SELECT name,code,amount
FROM yourTable
WHERE code IN (CODES NOT  TO SUMMARIZE)



Cheers.
0
 
LVL 8

Accepted Solution

by:
raulggonzalez earned 500 total points
ID: 34093519
CODES TO SUMMARIZE can be a table like @deighton said or parameters

(@param1, @param2 ...)

SELECT name,code,sum(amount)
FROM yourTable
WHERE code IN (CODES TO SUMMARIZE)
GROUP BY code, name
UNION

SELECT name,code,amount
FROM yourTable
WHERE code NOT IN (CODES TO SUMMARIZE)  -- Better like this.....


Cheers
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 34093669
add the Order By clause after the Union Select.
SELECT name,code,sum(amount)
FROM yourTable
WHERE code IN (CODES TO SUMMARIZE)
GROUP BY code, name
UNION

SELECT name,code,amount
FROM yourTable
WHERE code NOT IN (CODES TO SUMMARIZE)
ORDER BY name, code
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34093707
Hi @dmlyo150,

you win... but @RustyZ32 didn't mention anything about ordering....


Cheers.

0
 
LVL 6

Author Comment

by:RustyZ32
ID: 34096160
dmly0150

that worked!

however, I also have several other columns I would like to include in the select statement but when i put them in the GROUP BY clause it messes up the sums. (i get an errror if i dont put them in group by)

I know this isn't part of the original question and will accept your solution regardless.


thanks!
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 34096287
thank you -but I can stil help.
There is a PARTITION feature as of SQL Server 2005.
can you provide me (all) the field names and an idea of how you want it to apear with the addtional fields?
0
 
LVL 6

Author Comment

by:RustyZ32
ID: 34098691
dmlyo150, I figured it out. In the first select statement i left out any columns that I knew had unique data. the ones that didn't (i.e. an employees office) i was able to include without messing up the grouping.

i then specified the rest in the second statement after the union.


all worked well, thanks for your help.
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 34098711
Glad I can help -and thank you -every point helps
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34100059
Hi,

I have to add that the solution posted by @dmlyo150 is a copy/paste of my solution, so the accepted answer should be mine.

I want to open this question to be reviewed by a moderator.


Cheers
0
 
LVL 18

Expert Comment

by:deighton
ID: 34100663
raulg - I'm with you on that - it looks like a copy of your post with little further amplification

I respect the fact that you mentioned that your explanation added to my previous answer BTW, that's the way it should be, it was hard to see what was needed here.
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34100676
Hi @deighton

Thanks for your support. And apart, if you read the whole thread, he was asking me whether it was possible to use GROUP BY and ORDER BY together...

Cheers.
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 34101772

Administrative Moderator,
I accept your position, and thank you for imparting the Experts Exchange etiquette. I urge the moderator to review the initial post of this thread. I answered the Question! Correctly! Later the question was expanded requiring use of a UNION; this provided RustyZ32 with all the data (he) was looking for –which was NOT in the original question –but it still wasn’t summarized as he indicated it needed to be in his original question. Once again –I indicated using the Order by Clause –which he must have felt was pretty crucial because he awarded me the points.
 
---
raulggonzalez:
Hi @dmlyo150,

you win... but @RustyZ32 didn't mention anything about ordering....

Cheers.
---
RustyZ32 did say he wanted it order when he wrote… “a single select statement that will add all the amounts of each distinct code and also have it grouped by name”
If RustyZ32 did not put an Order By clause on this –it would have been grouped –first by code then by name. My Order By clause returned it grouped by name then code –as his original question asked.
_alias99 wrote that the best practice would have been to post - (for example, with a comment to the effect of "adding 'xyz' to the code posted by 'ExpertA' would be beneficial because ...").
My post read exactly that –my copy/paste was proceeded by -- add the Order By clause after the Union Select.
In all fairness I think RustyZ32 should at least have the opportunity to distribute the points to more than one contributor.
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34101895
Hi @dmlyo150,

When I said 'you win' was an irony.

There's nothing in @RustyZ32 original or 2nd question about ordering, that's why I gave up, because you where saying the same like I did + ORDER BY  TWO TIMES, which as long as I read, wasn't necessary.

And your ORDER BY is indeed mine as well, because you were asking me whether you can use both, and I said yes.

I don't want to get deeper and the moderator explained what should be pretty well. I'm not in this website since long, but I got how it works.

Best regards.

 
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 34102107
"And your ORDER BY is indeed mine as well, because you were asking me whether you can use both, and I said yes."

I don't know why you would think I was asking -another contributor -questions about code. I was asking RustyZ32 if there was a limitation to this code that he could NOT use an ORDER BY clause.
for example –It is possible an author may not give you the entire scenario –for instance if he was going to be using this Single Select as a subquery in his final solution –he would be unable to use an Order By clause. I am not naive enough to think that this one and only Select statement is going to complete his entire project.

Sincere Wishes
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Activity Monitor detail 2 23
RESTORE A BACKUP IN SQL 2012 from SQL 2008 9 64
SQL R 21 23
Parse this column 6 24
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question