Databases
StackOverflow2013
Postsdbo
POT-SQL count number of fields associated with a GUID and get sum of fields from another table associated with the same GUIDs
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL count number of fields associated with a GUID and get sum of fields from another table associated with the same GUIDs
    primarykey
    data
    text
    <p>EDIT: Hey Everyone,</p> <p>I figured it out</p> <pre><code>select LI.ProjectId, count(*) as numLineItems into #temp from dbo.SQLLineItems as LI group by LI.ProjectId having COUNT(*) &lt; =200 select numLineItems, count(*), SUM(PT.GrandTotalSell) from #temp inner join dbo.SQLProjectTotals as PT on PT.ProjectId = #temp.ProjectId group by numLineItems; </code></pre> <p>Thank you for your help.</p> <hr> <p>I have two tables that contain project information. Table 1 contains LineItems each with a GUID that is unique to that table and GUID for ProjectID. There are duplicates of each ProjectID since there can be multiple LineItems for one ProjectID.</p> <p>Table 2 is linked to Table 1 by Project ID and the GUID is unique in that table. Table 2 also contains the price of each project.</p> <p>I wanted to breakdown the projects by number of LineItems to see how many projects are 1, 2, 3, 4... item projects.</p> <p>I wrote a query that works:</p> <pre><code>use FieldedProjects; select LI.ProjectId, count(*) as numLineItems into #temp from dbo.SQLLineItems as LI group by LI.ProjectId having COUNT(*) &lt; =15 select numLineItems, count(*) from #temp group by numLineItems; </code></pre> <p>but now I want to modify it to show the price totals. So the total project prices for 1 item projects is 100, 2 item projects is 300,...</p> <p>I was thinking something like</p> <pre><code>use FieldedProjects; select LI.ProjectId, count(*) as numLineItems, SUM(PT.GrandTotalSell) as projectTotal into #temp from dbo.SQLLineItems as LI inner join dbo.SQLProjectTotals as PT on LI.ProjectID = PT.ProjectID group by LI.ProjectId having COUNT(*) &lt; =15 select numLineItems, count(*), projectTotal from #temp group by numLineItems; </code></pre> <p>But it gives me an error because projectTotal is not contained in an aggregate function. Is it supposed to be </p> <pre><code>select numLineItems, count(*), SUM(projectTotal) from </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAdam
    1. USAdam
    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. CO`group by numLineItems, projectTotal` -- will it work?
      singulars
      1. POT-SQL count number of fields associated with a GUID and get sum of fields from another table associated with the same GUIDs
      1. USJohn Woo
    2. COlets say you have three 2-item projects, with 10, 20, 30 as projectTotals. In your result you want to see a row with 2 as a [2-item], 3 as countOf2ItemProjects, and ??? do you want those projectTotals to be summed again or string representation of comma delimited numbers?
      singulars
      1. POT-SQL count number of fields associated with a GUID and get sum of fields from another table associated with the same GUIDs
      1. USuser2065377
    3. COI want one sum for the project totals and a column next to it that is the sum of the GrandTotalSell for each project that was counted. So what is the number of n item projects and what is the sum of the GrandTotalSells for all n item projects? I will try Roman's answer and see what comes of that. It may also be as simple as group by numLineItems, projectTotal
      singulars
      1. POT-SQL count number of fields associated with a GUID and get sum of fields from another table associated with the same GUIDs
      1. USAdam
 

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