CODEWITHSUNDEEP
sqlsql-servert-sql
Vyff
Vyff

Reputation: 83

How can create a table that uses an aggregate of distinct values across multiple columns as a parameter?

I would like to create a table that takes the distinct combinations of 2 columns and only returns those that are greater than one.

What I have so far creates a table of all companies and contracts associated with them.

Company  Contract
-------  -------- 
ABC      0000111
ABC      0000113
DEF      0000124
GHI      0000207
GHI      0000389
GHI      0000567

It also creates a second table that finds distinct combinations of those columan and creates a list of how many times they appear.

Company  Countr
-------  -------- 
GHI      3
ABC      2

I am trying to make a third table that returns just those rows that have companies matched with more than one contract.

Company  Contract
-------  -------- 
ABC      0000111
ABC      0000113
GHI      0000207
GHI      0000389
GHI      0000567

Modified source, if it helps

SELECT DISTINCT inventory.company, inventory.contracts
FROM inventory
WHERE inventory.company not in ('Company Name')
( 
    SELECT
       DISTINCT inventory.company,
       count(DISTINCT(concat(inventory.company, inventory.contracts))) AS Countr
    FROM inventory
    WHERE
    inventory.company not in ('Company Name')
    GROUP BY company 
    HAVING count(DISTINCT(concat(inventory.company, inventory.contracts))) > 1 
)
ORDER BY Countr DESC

Upvotes: 2

Views: 64

Answers (2)

Ely
Ely

Reputation: 11162

I am not sure if it is necessary to create more than one table. Considering the information you gave I would do it as follows:

Create a table with company and contract information and call it TABLE

Company  Contract
-------  -------- 
ABC      0000111
ABC      0000113
DEF      0000124
GHI      0000207
GHI      0000389
GHI      0000567

Then I would create a view on that table as follows and call it VIEW1; the columns are called COMPANY and CONTRACT_COUNT, for example:

select company, count(contract) from TABLE group by company;

Then I would create another view, VIEW2, with different query:

select distinct TABLE.company, TABLE.contract from TABLE, VIEW1 where TABLE.COMPANY=VIEW1.COMPANY and CONTRACT_COUNT > 1

Upvotes: 1

EoinS
EoinS

Reputation: 5482

Something like this should work:

SELECT * FROM inventory WHERE company in 
(SELECT company FROM inventory GROUP BY company HAVING count(distinct contract)>1);

The sub-query just creates a list of companys that have more than one contract associated with them. Then selects all data inventory that matches those companys. The DISTINCT modifier in the Count in the HAVING clause makes sure that there are multiple distinct contracts

Here's a link to the functional schema and query

Upvotes: 2

Related Questions