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: Thursday, August 27, 2015 2:52 PM
Points: 359, Visits: 437
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 @ 7:41 AM
Points: 13,706, Visits: 35,055
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 2,193, Visits: 23,188
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: Yesterday @ 1:31 PM
Points: 2,810, Visits: 6,170
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