Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 432, Visits: 570
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 @ 8:30 AM
Points: 14,458, Visits: 37,972
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1464823
Posted Tuesday, June 18, 2013 12:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 3,106, Visits: 30,831
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:06 PM
Points: 3,155, Visits: 6,556
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? Everybody look what's going down. -- Stephen Stills
Post #1464833
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse