group by clause doubt! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

group by clause doubt!

Author

Topic

ramalins
Starting Member

8 Posts

Posted - 2010-09-10 : 02:18:12

i have table called table1 and it looks like the below
record type tran_ref_number amount customer_name

TRN 123 15000 sara
TRN 234 25000 inba
TRN 345 20000 rajiv
TRN 456 16000 rahul
TRN 567 34556 sathish
TRN 678 15000 ilango
TRN 123 15000 sara
TRN 234 25000 inba
TRN 345 20000 rajiv
TRN 456 16000 rahul
TRN 567 34556 sathish
TRN 678 15000 ilango

i want to delete records from this table on the basis of "if the sum of the amount is exceeding 70000/per customer".
the result should be grouped on the basis of customer name.

Any one who has any idea about this can help me out on this issue.

thanks in advance.

saravanan ramalingam,
mumbai

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-09-10 : 02:54:51

Is this it...? ->

DELETE table1 WHERE customer_name IN (
  SELECT customer_name
  FROM table1
  GROUP BY customer_name
  HAVING SUM(amount) > 70000
  )

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

ramalins
Starting Member

8 Posts

Posted - 2010-09-10 : 03:10:41

the problem is if execute Lumbago's code then it deletes customers whose amount is still less than 70000 because it directly sees the name and deletes where ever the name is ......... i need to delete only those customer record who doesnt satisfy the > 70000 condition...

saravanan ramalingam,
mumbai

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-09-10 : 03:24:41

So change the > to a <...? Or am I missing something? Can you put a * or something behind the rows you want to be deleted?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

ramalins
Starting Member

8 Posts

Posted - 2010-09-10 : 04:27:48

Hi Lumbago,

i have the table like this..... i want to delete 'all'records where (sum of the local amount exceeds 70,000 per customer)

SiteId TillId ShiftId TransId (customer name) LocalAmount
1918 99 766 4864 SANJAY 58600.00
1918 99 766 4864 SANJAY 77000.00
1918 99 768 4867 MILAN 15990.00
1918 99 768 4867 MILAN 50200.00
1918 99 769 4868 THOMAS 73650.00
1918 99 769 4869 THOMAS 14000.00
1918 99 769 4869 THOMAS 37320.00
1918 99 769 4869 THOMAS 59245.50
1918 99 770 4872 SANJAY 3968.00
1918 99 770 4872 SANJAY 75500.00
1918 99 772 4879 MILAN 25000.00

saravanan ramalingam,
mumbai

Sachin.Nand

2937 Posts

Posted - 2010-09-10 : 04:35:21

i want to delete 'all'records where (sum of the local amount exceeds 70,000 per customer)

If I rephrase the above to this.

i want to delete 'all the customers' where sum of the local amount exceeds 70,000 per customer

Will it be the same? If it is then what Lumbago suggested will work.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

ramalins
Starting Member

8 Posts

Posted - 2010-09-10 : 06:28:14

consider this one... and tell me what can be done..

Transaction_Ref_Number Customer_Name Amount_INR
6443 GLOBE IOP 14280
6443 GLOBE IOP 67141
6441 GLOBE OP 14280
6444 GLOBE OP 14280
6446 GLOBE OP 14280
6597 GLOBE OP 14575
6598 GLOBE OP 14575
6752 GLOBE OP 14580
6753 GLOBE OP 14580
6754 GLOBE OP 14580
6621 GLOBE OP 14635
6622 GLOBE OP 14635
6785 GLOBE OP 14800
6824 GLOBE OP 14815
6834 GLOBE OP 14815
6785 GLOBE OP 41465
6824 GLOBE OP 61083
6441 GLOBE OP 67141
6444 GLOBE OP 67141
6446 GLOBE OP 67141
6597 GLOBE OP 68385
6598 GLOBE OP 68385
6752 GLOBE OP 68551
6753 GLOBE OP 68551
6754 GLOBE OP 68551
6621 GLOBE OP 68667
6622 GLOBE OP 68667
6834 GLOBE OP 80753
-----------------------------------------------
query i tried---

select Transaction_Ref_Number,Customer_Name,Amount_INR
from captrn
group by Customer_Name,Amount_INR,Transaction_Ref_Number
having sum(Amount_INR) < 1000000
------------------------------------
My question is will the above query i have written will give the same number of records what i expect in the below query
select Customer_Name
from captrn
group by Customer_Name
having sum(Amount_INR) < 1000000
---------------------------------
actually what is the problem in executing the Lumbago's query is if simple take the name and delete on the basis it will delete all records in the table when ever there is a match with the name which is not expected... thats why i want to add some more check like site id, till id, shift id, trans id....... to match the column with parent table..

when ever i simple put customer name in group by clause i get less record. but when ever i group by site , till, shift, trans id s it gives more records .........how is group by and having working on this scenario?

saravanan ramalingam,
mumbai

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-10 : 12:48:59

The people that help out here are all un-paid volunteers. Providing the DDL (CREATE TABLE, CREATE INDEX, etc.), DML (INSERT statements) and Expected Output will go a long way in getting people to look at your issue and help you out. That way we can run our code against your data and the benefit to you is you get working code back. Help us to help you by posting your question in a VERY clear and concise manner.

This link can help your prepare your question including DDL and DML:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

That having been said, can you write a query that will SELECT the records you want to delete? If so, post that along with yout DDL, DML and expected output and we can help you turn that into a delete statement.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-10 : 13:24:02

I see the problem

YOU need to come up with a definitive business requirement

We can't GUESS what you really want

And who is this guy Brett anyway?

Did you read the link that Lumbago posted?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-09-10 : 13:40:25

Can you put a * or something on the rows you want to be deleted!!!

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-09-10 : 15:30:26

quote:
Originally posted by ramalins

select Transaction_Ref_Number,Customer_Name,Amount_INR
from captrn
group by Customer_Name,Amount_INR,Transaction_Ref_Number
having sum(Amount_INR) < 1000000
------------------------------------
My question is will the above query i have written will give the same number of records what i expect in the below query
select Customer_Name
from captrn
group by Customer_Name
having sum(Amount_INR) < 1000000

Those 2 queries are different. The first one doesn't make a lot of sense. Including the Transaction_Ref_Number in the Group By means every record will be its own group so there is no point in doing that.

ramalins
Starting Member

8 Posts

Posted - 2010-09-13 : 01:12:34

Apologies to every one.... i am not intend to confuse anything or filling the spaces in this forum.. my query and related issues were with group by clause and having clause. My intension is to get a answer if possible by the people who can help me out..

saravanan ramalingam,
mumbai

ramalins
Starting Member

8 Posts

Posted - 2010-09-13 : 01:16:45

thank you very much for your answer...

quote:
Originally posted by denis_the_thief

quote:
Originally posted by ramalins

select Transaction_Ref_Number,Customer_Name,Amount_INR
from captrn
group by Customer_Name,Amount_INR,Transaction_Ref_Number
having sum(Amount_INR) < 1000000
------------------------------------
My question is will the above query i have written will give the same number of records what i expect in the below query
select Customer_Name
from captrn
group by Customer_Name
having sum(Amount_INR) < 1000000

Those 2 queries are different. The first one doesn't make a lot of sense. Including the Transaction_Ref_Number in the Group By means every record will be its own group so there is no point in doing that.

saravanan ramalingam,
mumbai

ramalins
Starting Member

8 Posts

Posted - 2010-09-13 : 01:18:42

thank you very much for your immediate answer.

quote:
Originally posted by Lumbago

Is this it...? ->
DELETE table1 WHERE customer_name IN (
SELECT customer_name
FROM table1
GROUP BY customer_name
HAVING SUM(amount) > 70000
)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

saravanan ramalingam,
mumbai

ramalins
Starting Member

8 Posts

Posted - 2010-09-13 : 01:22:39

hey...thanks for the answer....i am also aware of the way i have to post the query which one i am doing for years and years if i have doubts. i thought posting alone the simple data structure will give the users who try to give a answer a reference to the question.

quote:
Originally posted by Lamprey

The people that help out here are all un-paid volunteers. Providing the DDL (CREATE TABLE, CREATE INDEX, etc.), DML (INSERT statements) and Expected Output will go a long way in getting people to look at your issue and help you out. That way we can run our code against your data and the benefit to you is you get working code back. Help us to help you by posting your question in a VERY clear and concise manner.

This link can help your prepare your question including DDL and DML:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

That having been said, can you write a query that will SELECT the records you want to delete? If so, post that along with yout DDL, DML and expected output and we can help you turn that into a delete statement.

saravanan ramalingam,
mumbai

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-09-13 : 09:22:30

quote:
Originally posted by ramalins

Apologies to every one.... i am not intend to confuse anything or filling the spaces in this forum.. my query and related issues were with group by clause and having clause. My intension is to get a answer if possible by the people who can help me out..

saravanan ramalingam,
mumbai

The Having Clause is similar to Where Clause but Where Clause is a record condition and Having Clause is a Group Condition.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources