-2

I have a table 

+--------+--------+--------+--------+--------+
| Market | Sales1 | Sales2 | Sales3 | Sales4 |
+--------+--------+--------+--------+--------+
|     68 |      1 |      2 |      3 |      4 |
|    630 |      5 |      3 |      7 |      8 |
|    190 |      9 |     10 |     11 |     12 |
+--------+--------+--------+--------+--------+

I want to find duplicates between all the above sales fields. In above example markets 68 and 630 have a duplicate Sales value that is 3.

My problem is displaying the Market having duplicate sales.

OGHaza
  • 4,795
  • 7
  • 23
  • 29
Ahmed
  • 5
  • 3
  • You can use a CTE to get this information and join to the CTE to get the market. – JonH May 28 '14 at 19:11
  • 2
    What are the expected results in this case? I'm not sure what you mean by duplicate. so is sales3 for market 68 and 30 duplicate because of the 30?? or is market 30 duplicated because of 10, 10 in sale1 and sale2? In the raw data I see no duplicates across all rows. or is market 68 and 30 duplicates because they exist twice irrespective of the sales data? – xQbert May 28 '14 at 19:12
  • This would be super trivial if you normalised you table so you just had a `Market` and a `Sales` column, then 4 rows for each of your current rows. – OGHaza May 28 '14 at 19:28

2 Answers2

0

Supposing the data size is not so big, make a new temporay table joinning all data:

Sales 
Market

then select grouping by Sales and after take the ones bigger than 1:

select Max(Sales), Count(*) as Qty 
from #temporary 
group by Sales
Rogério Silva
  • 121
  • 1
  • 11
0

This problem would be incredibly simple to solve if you normalised your table.

Then you would just have the columns Market | Sales, or if the 1, 2, 3, 4 are important you could have Market | Quarter | Sales (or some other relevant column name).

Given that your table isn't in this format, you could use a CTE to make it so and then select from it, e.g.

WITH cte AS (
    SELECT Market, Sales1 AS Sales FROM MarketSales
    UNION ALL 
    SELECT Market, Sales2 FROM MarketSales
    UNION ALL 
    SELECT Market, Sales3 FROM MarketSales
    UNION ALL 
    SELECT Market, Sales2 FROM MarketSales
)
SELECT a.Market
      ,b.Market
FROM cte a
INNER JOIN cte b ON b.Market > a.Market
WHERE a.Sales = b.Sales

You can easily do this without the CTE, you just need a big where clause comparing all the combinations of Sales columns.

OGHaza
  • 4,795
  • 7
  • 23
  • 29
  • 1
    Thank you @OGHaza. Is it possible to show the duplicate sales as well alongwith Market? In the example it is Sales3 of Market 68 and Sales2 of Market 630 – Ahmed May 28 '14 at 19:50