Not sure how to do this - 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

General SQL Server Forums

New to SQL Server Programming

Not sure how to do this

Author

Topic

bluenotebooks
Starting Member

3 Posts

Posted - 2014-05-20 : 18:24:58

I have two columns, one containing a credit card number(lets say we have five different credit card numbers), and the other column containing the merchant name where the card was used. Is there way for me run a query to see each merchant name where all five cards have been used? I'm trying to identify a common point of purchase among all five cards. Hope this makes sense. If it doesn't just let me know and I will try to explain better. I'm pretty new to using SQL and have tried everything I know how to do with no luck. I can use the COUNT function to see how many transactions were done total at each merchant but that doesn't tell if all five of the cards were actually used at the merchant perhaps just one card was used there 5+ times.

Thanks for any advice you can offer.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-20 : 22:54:58

maybe this:


select   merchantName, count(distinct CCNum) as UniqueCCUsed
from     yourTable
group by merchangeName
having count(distinct CCNum) >= 5

Be One with the Optimizer
TG

GouravSaxena1987
Starting Member

23 Posts

Posted - 2014-05-21 : 02:20:18

Hello,

Below is solution for you :

SELECT
MarchantName,
COUNT(CreditCardNumber )
FROM (
SELECT DISTINCT MarchantName , CreditCardNumber FROM [dbo].[CreditCard_Information]
) AS
aa GROUP BY MarchantName HAVING COUNT(CreditCardNumber)>=5

Regards,
Gourav Saxena
Data Warehouse Counsultant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources