Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need assistance with a query Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 11:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:58 AM
Points: 334, Visits: 392
Is there a way to structure a single query to select all the unique values from field 1 of table A, and the unique values from field 2 of Table A and then select count(*) for all of the resulting combinations of field 1 and field 2 values?

Thanks in advance.
Post #1464819
Posted Tuesday, June 18, 2013 12:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 12,927, Visits: 32,330
RPSql (6/18/2013)
Is there a way to structure a single query to select all the unique values from field 1 of table A, and the unique values from field 2 of Table A and then select count(*) for all of the resulting combinations of field 1 and field 2 values?

Thanks in advance.


maybe something like this?
the UNION will merge duplicates between the two tables.
SELECT 
COUNT(Columnname) As TotalCount
FROM (
SELECT DISTINCT ColumnName FROM Table1
UNION
SELECT DISTINCT OtherColumnName FROM Table2
)MyAlias



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1464823
Posted Tuesday, June 18, 2013 12:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 1,935, Visits: 19,986
starter......


SELECT TOP 10000
CustomerID = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
ProdID = CAST(Abs(Checksum(Newid()) % 900 + 1) AS INT)
INTO TestData
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3

SELECT DISTINCT CustomerID
FROM TestData

SELECT DISTINCT ProdID
FROM TestData

SELECT COUNT(DISTINCT CustomerID) AS Expr1, COUNT(DISTINCT ProdID) AS Expr2
FROM TestData





______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1464825
Posted Tuesday, June 18, 2013 12:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
If you want a count of all the combinations, just multiply the count of distinct table 1 values times the count of distinct table 2 values.

declare @table1 table (value1 char(1))
declare @table2 table (value2 char(1))

insert into @table1
values ('A'),('A'),('B'),('C'),('D'),('D'),('A'),('A') -- 4 distinct values


insert into @table2
values ('X'),('Y'),('Y'),('Y'),('Z'),('1'),('2'),('3') -- 6 distinct values

select (select count(distinct value1) total1 from @table1) *
(select count(distinct value2) as total2 from @table2) as Combinations


edit: simplified final query


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1464833
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse