Databases
StackOverflow2013
Postsdbo
POSQL Sum in Multiple join query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Sum in Multiple join query
    primarykey
    data
    text
    <p>I'm struggling for the setup for this. This is the setup of the first table. The first query needs to only apply to items past a certain date, say 2012-06-01. </p> <pre><code>| Table: TIMELOG | | INQUIRY_ID | … | LOGMINS | … | OPERID | … | EDATE | | 2 | | 45 | | bob | | 2012-05-01 | | 5 | | 3 | | richard | | 2012-06-02 | | 5 | | 12 | | bob | | 2012-07-01 | | 5 | | 15 | | paul | | 2012-07-01 | | 6 | | 10 | | paul | | 2012-07-01 | </code></pre> <p>The query then needs to go into a different table using INQUIRY_ID and get some additional fields (there is only one entry per INQUIRY_ID in this next table:</p> <pre><code>| Table: INQUIRY | | INQUIRY_ID | … | CATEGORY_ID | … | PROD_ID | | 2 | | 45 | | 6 | | 3 | | 3 | | 50 | | 4 | | 12 | | 3 | | 5 | | 15 | | 67 | | 6 | | 10 | | 2 | </code></pre> <p>Once we have the CATEGORY_ID and PROD_ID the "friendly names" for those are in their own seperate tables</p> <pre><code>| Table: CATEGORY | | CATEGORY_ID | … | CATNAME | | 45 | | Server | | 3 | | Workstation | | 12 | | Phones | | 15 | | Backup | | 10 | | Network | | Table: PROD | | PROD_ID | … | PRODDESC | | 6 | | SBS 2003 | | 50 | | Windows 7 | | 3 | | iPhone | | 67 | | Buexec | | 2 | | SwitchF | </code></pre> <p>I'd then like to total the LOGMINS field, bearing in mind sometimes it will have multiple LOGMINS per INQUIRY_ID To finish up as</p> <pre><code>| INQUIRY_ID | … | TOTAL | … | CATNAME | … | PRODDESC | | 2 | | 45 | | Server | | SBS 2003 | | 5 | | 30 | | Workstation | | Windows 7 | | 6 | | 10 | | Phones | | iPhone | </code></pre> <p>I'm sorry for the extraordinary amount of detail, the queries I have tried just bomb out, my skill in SQL blows up when I get into nested queries and multiple joins. Any help would be massively appreciated.</p> <p>Query I am running now:</p> <pre><code> select timelog.INQUIRY_ID, SUM (logmins) AS Total, catname, proddesc, EDATE from timelog inner join inquiry on timelog.inquiry_id=inquiry.inquiry_id inner join category on inquiry.category_id = category.category_id inner join prod on inquiry.prod_id = prod.prod_id where EDATE &gt; '2013-07-01' group by timelog.INQUIRY_ID, catname, proddesc, edate order by timelog.INQUIRY_ID desc </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTrinitrotoluene
    1. USTrinitrotoluene
    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
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

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