CODEWITHSUNDEEP
sqlcountif
user7729132
user7729132

Reputation:

SQL - Count instances in 1 field with variations in 2nd

Thanks for taking a second to read this... Have tried this in SQL, Python, and VBA with no luck (various reasons).

The data and concept are, I think, pretty simple - but I can't seem to make it work.

Column 1 has a stock market ticker; Column 2 has the company name. However, many of the company names have been truncated, or have changed over time. I want to find every instance of each ticker, where that ticker has more than 1 name.

So for example, my file has these 4 lines

IBM | Int Bus Mach
IBM | International Business M
IBM | Intl Bus Machines
IBM | Int Bus Mach

I would like to see the 3 unique company names

IBM | Int Bus Mach
IBM | International Business M
IBM | Intl Bus Machines

Any ideas are certainly appreciated!

Thanks!

Upvotes: 0

Views: 42

Answers (3)

Ven
Ven

Reputation: 2014

May be try something like this. Would bring distinct names. 

    WITH cte
AS (
    SELECT market_ticker
        ,Company_name
    FROM Stocks
    )
SELECT a.market_ticker
    ,b.Company_name
FROM cte a
INNER JOIN stocks b
    ON a.market_ticker = b.market_ticker
GROUP BY a.market_ticker
    ,b.company_name
HAVING count(a.company_name) >= 2
ORDER BY 1
    ,2

Upvotes: 0

Andrew S
Andrew S

Reputation: 36

2 step Sql Solution here. I would do the following

Simple distinct query to get all ticker and company combos then pivot

select *,
ROW_NUMBER() OVER(PARTITION BY TickerColumn ORDER BY NameColumn) AS NameNum
into #Temp
From 
(
Select distinct TickerColumn, NameColumn
from Table
)x

Then Pivot ColumnNames

Select TickerColumn, [1],[2],[3],[4]
From
(select * from #temp) as Source
Pivot
(
Max(NameColumn)
For NameNum in ([1],[2],[3],[4]) ---You can add or reduce number of columns
) As PivotTable;

Upvotes: 0

danielsepulvedab
danielsepulvedab

Reputation: 674

According to your data examples, you should do something like this:

SELECT
    market_ticker,
    company_name,
    count(*)
FROM yourTable
GROUP BY
    market_ticker,
    company_name

The extra column will give how many times the market ticker and company name repeat.

Upvotes: 1

Related Questions