Databases
StackOverflow2013
Postsdbo
POCounting rows in many to many tables
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCounting rows in many to many tables
    text
    copied!<p>Please help me with the following situation:</p> <p>In SQL Server 2008 I have a database with the tables:</p> <ul> <li><strong>Installations</strong> (<code>id, name</code>) which is unique for a given system</li> <li><strong>Products</strong> (<code>Id, name</code>) which are installed on a system</li> </ul> <p>And because a system has more products and the same product can be installed on multiple machines there is <code>InstallationXProduct</code> table withe their ids. </p> <p>Another table <code>Usages</code> (<code>usageid, productid, date</code> etc) is linked to a product to keep track of product usages. </p> <p>I need a script to link <code>Installation</code>, <code>Products</code> and <code>Usage</code> count for each <code>Installation-Product</code> pair, e.g.</p> <pre><code>Installation 1 Product A 2 Installation 1 Product B 3 Installation 2 Product A 1 Installation 2 Product C 2 </code></pre> <p>Thank you for any help</p> <p>Later EDIT:</p> <p>Tables:</p> <ul> <li><code>Installation</code>: <code>InstallationID, InstallationName</code></li> <li><code>Product</code>: <code>ProductID, ProductName</code></li> <li><code>InstallationXProduct</code>: <code>InstallationID, ProductID</code></li> <li><code>Usage</code>: <code>UsageID, DateRecorded, ProductID</code> </li> </ul> <p>Set up data:</p> <pre><code>Insert into Installation values(1,'A') Insert into Installation values(2,'B') Insert into Product values (1,'P1') Insert into Product values (2,'P2') Insert into InstallationXProduct values (1,1) Insert into InstallationXProduct values (2,1) Insert into InstallationXProduct values (1,2) Insert into InstallationXProduct values (2,2) Insert into Usage values(1,getdate(),1) Insert into Usage values(2,getdate(),2) Insert into Usage values(3,getdate(),1) </code></pre> <p>Query to select:</p> <pre><code>select I.InstallationID, P.ProductID, count(U.UsageID) from Installation I join InstallationXProduct IXC on I.InstallationID = IXC.InstallationID join Product P on P.ProductID = IXC.ProductID join Usage U on U.ProductID = P.ProductID group by I.InstallationId,P.ProductID </code></pre> <p>returns:</p> <pre><code>1 1 2 2 1 2 1 2 1 2 2 1 </code></pre> <p>After reading this light version I think I understand why it is not possible to split the usages in products and installations, there is no way I can tell whether usage one was recorded on product 1 from installation 1 or 2. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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