how to count primary key of one table inanother table?

Question

how to count primary key of one table which is used in another table in 5 to 6 column?

If table 1 has primary key srno which is used in table2 in column d1,d2,d3,d4.

I want to count how many times srno=1,2,3,4... etc used in table2.

any one know how to do that???

Edit your question and add the CREATE TABLE statements, a few rows with sample data and what you want as output. If you have a query you tried, add that, too. You can also use SQL-Fiddle and add here a link to the page you created. — ypercubeᵀᴹ, May 28 '13 at 12:51
If the same srno value is used multiple times in a single row, does that count as 1, or does it count as the number of times it appears? Also, which RDBMS? — Jon Seigel, May 28 '13 at 14:14
This question appears to be off-topic because it was cross-posted at SO: http://stackoverflow.com/questions/16789024/how-to-count-primary-key-of-one-table-in-another-table — ypercubeᵀᴹ, Jun 27 '13 at 17:05

score 1 · Answer 1 · answered May 28 '13 at 15:05

Total of all columns regardless of duplicates.

SELECT count(d1) + count(d2) + count(d3) + count(d4) FROM Table2;

Total of each column regardless of duplicates.

SELECT count(d1), count(d2), count(d3), count(d4) FROM Table2;

Total of each column without duplicates.

SELECT 
     count(distinct d1)
   , count(distinct d2)
   , count(distinct d3)
   , count(distinct d4) 
FROM Table2;

Total of all columns without duplicates.

SELECT count(distinct d1) FROM
(
  SELECT d1 FROM Table2
  UNION 
  SELECT d2 FROM Table2
  UNION 
  SELECT d3 FROM Table2
  UNION 
  SELECT d4 FROM Table2
);

SQLFiddle

how to count primary key of one table inanother table?

1 Answers1