April 10, 2008 at 7:53 pm
Hello everyone,
I'm wondering how one should go about SUM'ing a column and then sorting the result set by the returned (SUM) values.
Let's say I have a table 'Voting'
PERSON varchar(25)
T_VOTES int
Let's say I have 5 people defined in the table but each person shows up 5 times with varrying values for T_VOTES (2-10 let's say)
I can...
SELECT PERSON, SUM(T_VOTES) AS TOTAL
FROM VOTING
GROUP BY PERSON
This gives me a single line for each person in the table with their total votes.
Regarding grouping and sorting, I have a few questions. I understand that the select clause and group clause must match, with agregates being the exception to the rule.
I also (believe I) understand that you cannot sort by or group by an aliased column (which I've done above).
So, in order to do something like this would you kick things off with an INSERT INTO in to a temporary table and then sort those results? Or is there a better / cleaner way to do this?
Thank you!
Bob
April 10, 2008 at 8:15 pm
One more question... (probably missing something very dumb here)
How can you have a result set with multiple columns if you can only group by a single column and the select clause and group clause must match?
April 10, 2008 at 8:22 pm
Interestingly enough - 2005 gives you more options for doing grouped sums. You don't actually need to use the GROUP BY clause - you can use a SUM() OVER syntax.
Now - keep in mind you don't get this "for free" - this is NOT the same as a traditional GROUP BY, so the performance may suffer.
Example:
select person,
time,
othercols,
T_votes,
sum(t_votes) OVER (PARTITION BY PERSON) as Total
from VOTING
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 10, 2008 at 10:02 pm
You can group by as many columns as you need.
Consider the following
Create table1 (col1 int,
col2 char (10),
col3 char (10),
col4 money)
You can use the following select
SELECT COL1, Col2, Sum (col4)
FROM Table1
Group by Col1, Col2
Or
SELECT Col1, Right (Col2, 3), Sum (col4)
from Table1
Group by Col1, Right (COl2, 3)
i.e. the group by simply needs to match the equivalent columns in the select list.
Normally, I would advise against using a temporary table if the requirement is simply to deal with naming of columns or apply some other busines rule/filter. I would use a derived table for this purpose
e.g.
SELECT *
FROM (SELECT COL1, Col2, Sum (col4) AS TotalAmount
FROM Table1
Group by Col1, Col2)
AS A
WHERE (col1 = 'My Value' and TotalAmount < 100)
OR (Col2 = 'another value' and TotalAmount = 200)
April 11, 2008 at 2:48 am
Any of these will do the sorting...
SELECT PERSON, SUM(T_VOTES) AS TOTAL
FROM VOTING
GROUP BY PERSON
ORDER BY SUM(T_VOTES)
SELECT PERSON, SUM(T_VOTES) AS TOTAL
FROM VOTING
GROUP BY PERSON
ORDER BY 2
SELECT PERSON, SUM(T_VOTES) AS TOTAL
FROM VOTING
GROUP BY PERSON
ORDER BY TOTAL
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply