CODEWITHSUNDEEP
sql-servert-sql
netfed
netfed

Reputation: 601

Counting fields in multiple tables

I have thhree tables:

Table Subjects: field name: term

Table Definitions: field name: what

Table Codeblocks: Field name: codeblock

Wanted the number of occurences of the three fields over three tables (counting). I wrote this sql query (it works alright btw): 

SELECT Entity, COUNT(FieldToCount) AS quantity
FROM (SELECT Entity, FieldToCount
   FROM (SELECT  'Subjects' AS Entity, COUNT(term) AS FieldToCount
       FROM          dbo.subjects
       GROUP BY term
       UNION ALL
       SELECT     'Definitions' AS Entity, COUNT(what) AS FieldToCount
       FROM         dbo.definitions
       GROUP BY what
       UNION ALL
       SELECT     'codeblocks' AS Entity, COUNT(codeblock) AS FieldToCount
       FROM         dbo.codeblocks
       GROUP BY codeblock) AS SUMTABLE) AS REPORT
GROUP BY Entity, FieldToCount

Got this result:

Entity      quantity
----------- -----------
codeblocks  3
Definitions 4
Subjects    2

.. in other words counting the occurences of a field in each of the three tables.

Now how could this query be written more compact or elegantly? Or the question rephrased: what is the minimal or more elegant way of counting the occurences of a field in multiple tables?

(in Transact sql)

Upvotes: 0

Views: 79

Answers (1)

tommy_o
tommy_o

Reputation: 3783

SELECT  'Subjects' AS Entity
        , count(term) AS FieldToCount
FROM     dbo.subjects
UNION ALL
SELECT   'Definitions' AS Entity
        , count(what) AS FieldToCount
FROM        dbo.definitions
UNION ALL
SELECT  'codeblocks' AS Entity
        , count(codeblock) AS FieldToCount
FROM     dbo.codeblocks

I was lazy and tested this against some sys tables:

SELECT  'Subjects' AS Entity
        , count(name) AS FieldToCount
FROM    sys.tables
--
UNION ALL
SELECT   'Definitions' AS Entity
        , count(name) AS FieldToCount
FROM    sys.columns
--
UNION ALL
SELECT   'AnotherChoice' AS Entity
        , count(object_id) AS FieldToCount
FROM    sys.tables

Not sure if you have other requirements, but this seems to work.

Upvotes: 1

Related Questions