t-sql puzzler
-
I'm working on a table called "GroupingTest", shown below.
myIDmyNamemyMoney
1manny12.00
3moe2.00
1jack3.00
5manny7.00
4moe8.00
4Fred9.00
9 Ida 18.00
(
CREATE TABLE [dbo].[GroupingTest](
[myID] [varchar](50) NULL,
[myName] [varchar](50) NULL,
[myMoney] [money] NULL
)
insert into GroupingTest values( '1','manny', 12.00)
insert into GroupingTest values( '3','moe', 2.00)
insert into GroupingTest values( '1','jack', 3.00)
insert into GroupingTest values( '5','manny', 7.00)
insert into GroupingTest values( '4','moe', 8.00)
insert into GroupingTest values( '4','Fred', 9.00)
insert into GroupingTest values( '9','Ida', 18.00)
)
I want to create another table consisting of all rows in GroupingTest,
where for a given "myID", the sum of all myMoney fields exceeds 13.00.
The expected output is this:
myIDmyNamemyMoney (Sum of all MyMoney fields)
1manny12.00 (15 = 12 + 3)
4Fred6.00 (17.00 = 8 + 9)
9 Ida 18.00 (18 > 13)
Note that for rows with myID = 1 (2 rows) the sum of MyMoney exceeds 13
but the output only returns a single (arbitrary) row with myID = 1.
(I'm more interested in the myID value, that after summation, exceeds 13 than
each row's content.)
Any ideas how to approach this problem?
TIA,
barkingdog
-
Here is one way of doing it:
with GetName as (
select myID, min(MyName) as MyName
from GroupingTest
group by myID)
select GetName.MyID, GetName.MyName, sum(MyMoney)
from GetName inner join GroupingTest on GetName.myID = GroupingTest.myID
group by GetName.MyID, GetName.MyName
having sum(MyMoney) > 13
go
By the way are you sure that you need to have the column MyName in the results? Maybe it will be better to leave this column outside of the results because the data that comes as sum(MyMoney) has noting to do with the name in MyName column.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Thaks for the interesting reply but when I ran your (CTE?) I got
1jack15.00
4Fred35.00
The first row is exactly what I want but the second row is not. I expected 17 (which is the sum of the myMoneys in these two rows.)
4 moe 8.00
4 Fred 9.00
I also noted that your t-sql groups by MyName. For various reasons, I really do NOT want that column to be included in the grouping--- I only want to rely on the MyID column.
Barkingdog
-
Barkingdog (12/15/2008)
Thaks for the interesting reply but when I ran your (CTE?) I got1jack15.00
4Fred35.00
The first row is exactly what I want but the second row is not. I expected 17 (which is the sum of the myMoneys in these two rows.)
4 moe 8.00
4 Fred 9.00
I also noted that your t-sql groups by MyName. For various reasons, I really do NOT want that column to be included in the grouping--- I only want to rely on the MyID column.
Barkingdog
I just re run the query with your script that creates the table and inserts the test data. I got the expected results:
1jack15.00
4Fred17.00
9Ida18.00
Did you modify my query? If you did, maybe you made a mistake while modifying it and this caused the different results that both of us are getting.
As for column MyName - In your first post you asked to include this column in the results. My query is grouping the data according to MyID column and shows you one of the names that appeared in MyName column, but the grouping is done only by MyID column. I also recommended in my post that you'll take out the column MyName. If you do want to take it out, you can modify my query so it will look like this:
select MyID, sum(MyMoney)
from GroupingTest
group by MyID
having sum(MyMoney) > 13
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Here's another way
with CTE as (select myID,myName,myMoney,
sum(myMoney) over(partition by myID) as sm,
row_number() over(partition by myID order by myMoney desc) as rn
from GroupingTest)
select myID,myName,myMoney
from CTE
where sm>13 and rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks for the replies. I'll need to digest them a bit.
BTW can any think of a solution not using a CTE?
Barkingdog
-
Have you tried this?
SELECT
myId,
SUM(myMoney) as Total,
MIN(myName) as myName
FROM [GroupingTest]
GROUP BY myId
HAVING SUM(myMoney) > 13
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
You could use a derived table in your FROM clause, but a CTE is cleaner.
-
am I missing something here!
Why do you need a derived table or a CTE when it seems to be a simply Group By Having Statement?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
Christopher Stobbs (12/15/2008)
am I missing something here!Why do you need a derived table or a CTE when it seems to be a simply Group By Having Statement?
You are not missing a thing. I started the CTE thing and the rest followed my answer. Of course there is no need to CTE and derived table.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply