CODEWITHSUNDEEP
sqlreporting-servicescount
user1212436
user1212436

Reputation: 27

Count Multiple Fields same Row - SQL

I have a question regarding counting multiple fields in the same row in the same table. The easiest way to show what I need is to provide an example. I have report_number, writer1, loc1, writer2, loc2 columns. You can call my table report_master. My table looks like this:

    Report_Number,  writer1,    loc1,   writer2,    loc2
    1               123         1       456         2    
    2               456         2       789         3   
    3               789         3       123         1    
    4               789         3       123         1    
    5               456         2       789         3    
    6               123         1       456         2    
    7               123         1       789         3    
    8               456         2       123         1    
    9               789         3       123         1    
    10              123         1       456         2

OUTPUT:

    Reports,    writer
    8           123    
    6           456    
    6           789

I have tried and tried and can not figure out the query to get the number of reports that a writer has been involved with. The writer will get credit for a report if they are in the writer1 or writer2 fields. I would like to base my query off of a date range and the loc. So if I enter a date range (this table does have a date field) and a loc # (includes searching both loc1 and loc2) I will get something that looks like the results table.

Thanks in advance for your help. I will be using this in SQL SSRS.

Upvotes: 1

Views: 2349

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52645

The most straight forward way is do a count of the union of a select of each writer field

SELECT COUNT(report_number) reports, 
       writer 
FROM   (SELECT report_number, 
               writer1  as writer
        FROM   table 
        UNION ALL 
        SELECT report_number 
               writer2 as writer
        FROM   table) t
GROUP  BY writer

Another option is to use the UNPIVOT Clause but its only really worth it if your UNION SELECTs aren't trivial

SELECT COUNT(report_number), 
       writer 
FROM   (SELECT report_number, 
               writer1, 
               writer2 
        FROM   reports) p UNPIVOT (writer FOR writer_type IN (writer1, writer2) 
       )AS 
       unpvt 
GROUP  BY writer

See working sample here

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135779

SELECT COUNT(*) AS Reports, q.writer
    FROM (SELECT writer1 AS writer
              FROM YourTable
              WHERE loc1 = 1
          UNION ALL
          SELECT writer2 AS writer
              FROM YourTable
              WHERE loc2 = 1) q
   GROUP BY q.writer

Upvotes: 1

Related Questions