Need assistance with a query

• RPSQL

  Hall of Fame

  Points: 3229

  More actions

  June 18, 2013 at 11:54 am

  #276471

  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.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 18, 2013 at 12:02 pm

  #1625174

  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!

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 18, 2013 at 12:10 pm

  #1625176

  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

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  June 18, 2013 at 12:40 pm

  #1625184

  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