Databases
StackOverflow2013
Postsdbo
POSQL to get separate counts of two groups across multiple tables?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL to get separate counts of two groups across multiple tables?
    primarykey
    data
    text
    <p>We have a small randomized study that we're trying to report numbers for. In this database, we have eight tables that contain different randomization groups (treatment vs. control) for each table that are designed like so:</p> <pre><code>+--------+-------+----------------------+-----------------+ | caseID | patID | randomizedDate | randomizedGroup | +--------+-------+----------------------+-----------------+ | 1 | 5000 | 2/17/2010 5:12:00 PM | T | | 2 | 5005 | 3/11/2010 1:45:00 PM | C | | 3 | 5007 | 3/22/2010 7:16:00 AM | C | | 4 | 5011 | 4/10/2010 3:34:00 PM | T | | 5 | 5015 | 4/19/2010 5:41:00 PM | C | | 6 | 5018 | 5/23/2010 4:06:00 PM | T | | 7 | 5021 | 6/27/2010 5:28:00 PM | T | | 8 | NULL | NULL | C | | 9 | NULL | NULL | T | | 10 | NULL | NULL | T | | 11 | NULL | NULL | C | | 12 | NULL | NULL | C | </code></pre> <p>The tables were already pre-generated with randomized Ts &amp; Cs using a stats program beforehand. So, we have eight groups of these that are waiting to be filled based on preset criteria for our project. No PatID will exist in more than one of these tables.</p> <p>What we need is a breakdown of counts across these tables based on the randomizedGroup column. For example:</p> <pre><code>+--------------------+--------+--------+--------+----------+ | randomizationGroup | Table1 | Table2 | Table3 | So on... | +--------------------+--------+--------+--------+----------+ | C | 10 | 24 | 14 | | | T | 11 | 16 | 21 | | +--------------------+--------+--------+--------+----------+ </code></pre> <p>As of now I'm getting these numbers using the following query, but I wanted to find out if this is optimal or if I should be doing it another way. The more I use SQL, the more I like it so I'm always wanting to refine my skills and learn.</p> <pre><code>SELECT randomizationGroup, SUM(count1) AS Table1, SUM(count2) AS Table2, SUM(count3) AS Table3, SUM(count4) AS Table4, SUM(count5) AS Table5, SUM(count6) AS Table6, SUM(count7) AS Table7, SUM(count8) AS Table8 FROM ( SELECT randomizationGroup, COUNT(*) AS count1, 0 AS count2, 0 AS count3, 0 AS count4, 0 AS count5, 0 AS count6, 0 AS count7, 0 AS count8 FROM Table1 WHERE patid IS NOT NULL GROUP BY randomizationGroup UNION ALL SELECT randomizationGroup, 0 AS count1, COUNT(*) AS count2, 0 AS count3, 0 AS count4, 0 AS count5, 0 AS count6, 0 AS count7, 0 AS count8 FROM Table2 WHERE patid IS NOT NULL GROUP BY randomizationGroup UNION ALL SELECT randomizationGroup, 0 AS count1, 0 AS count2, COUNT(*) AS count3, 0 AS count4, 0 AS count5, 0 AS count6, 0 AS count7, 0 AS count8 FROM Table3 WHERE patid IS NOT NULL GROUP BY randomizationGroup UNION ALL SELECT randomizationGroup, 0 AS count1, 0 AS count2, 0 AS count3, COUNT(*) AS count4, 0 AS count5, 0 AS count6, 0 AS count7, 0 AS count8 FROM Table4 WHERE patid IS NOT NULL GROUP BY randomizationGroup UNION ALL SELECT randomizationGroup, 0 AS count1, 0 AS count2, 0 AS count3, 0 AS count4, COUNT(*) AS count5, 0 AS count6, 0 AS count7, 0 AS count8 FROM Table5 WHERE patid IS NOT NULL GROUP BY randomizationGroup UNION ALL SELECT randomizationGroup, 0 AS count1, 0 AS count2, 0 AS count3, 0 AS count4, 0 AS count5, COUNT(*) AS count6, 0 AS count7, 0 AS count8 FROM Table6 WHERE patid IS NOT NULL GROUP BY randomizationGroup UNION ALL SELECT randomizationGroup, 0 AS count1, 0 AS count2, 0 AS count3, 0 AS count4, 0 AS count5, 0 AS count6, COUNT(*) AS count7, 0 AS count8 FROM Table7 WHERE patid IS NOT NULL GROUP BY randomizationGroup UNION ALL SELECT randomizationGroup, 0 AS count1, 0 AS count2, 0 AS count3, 0 AS count4, 0 AS count5, 0 AS count6, 0 AS count7, COUNT(*) AS count8 FROM Table8 WHERE patid IS NOT NULL GROUP BY randomizationGroup) all_groups GROUP BY randGroup </code></pre> <p>Thank you!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USRichard
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL to get separate counts of two groups across multiple tables?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat database engine and version are you using?
      singulars
      1. POSQL to get separate counts of two groups across multiple tables?
      1. USLamak
    2. COSQL 2005 Standard SP3 9.00.4053.00
      singulars
      1. POSQL to get separate counts of two groups across multiple tables?
      1. USRichard
    3. COCan you copy all the data into one table - with an extra column denoting he test name?
      singulars
      1. POSQL to get separate counts of two groups across multiple tables?
      1. USMark
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload