need SQL Query to location rows issue

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

General SQL Server Forums

New to SQL Server Programming

need SQL Query to location rows issue

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-07-16 : 22:24:51

HI I have a transactions table which has some of the rows have the
same invoicenum has more than 1 customerid row per invoicenum
this is 'not supposed' to happen.

So you have

invoicenum customerid

12345 1
12345 17

this is a condition I want to
write a query to produce a result set of all the rows that are in this problem that is where it's not like this.

12345 1
12346 17

I want all the ones with more than one customerid with the same invoicenum

I am thinking a select distinct or is there a nondistinct??

Select * from Transactions
where invoicenum in (TRansactions)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2011-07-17 : 04:29:37

If I understand your requirements correctly, what you need is a GROUP BY.

SELECT InvoiceNum, COUNT(*)
FROM TransactionTable
GROUP BY InvoiceNum
HAVING COUNT(*) > 1

The above will return any invoices that have more than one row. If you want to see the details of these invoices, you can join this query back to the transaction (or another table).

OS

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-07-17 : 11:25:45

Moh, more than one row is not an issue.
only if within more than one row, you have a Different customerid, that is multi custonerid's in the same invoicenum.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-17 : 11:33:24

Did you try his query? It gives you exactly what you asked for. You should try someone's query first before you dismiss it out of hand.

Jim

Everyday I learn something that somebody else already knew

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-07-17 : 14:06:20

yes it does work but its' not what I need.

Moh's query gives all invoices with more than one row.

I need that but only where the customer id's are different.

same invoice number, more than one customer id.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-17 : 14:38:39

SELECT InvoiceNum, COUNT(*)
FROM TransactionTable
GROUP BY InvoiceNum
HAVING COUNT(DISTINCT CustomerID) > 1

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-07-17 : 14:46:22

That's it Rob, thanks to you both, 1000 cool tokens to Rob and Moh.

quote:
Originally posted by robvolk

SELECT InvoiceNum, COUNT(*)
FROM TransactionTable
GROUP BY InvoiceNum
HAVING COUNT(DISTINCT CustomerID) > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources