How do I use the count function on an inner join properly to yield proper results

Question

0.00/5 (No votes)

See more:

I have written some sql code that is not yielding the proper results that I'm looking for. I have 2 table one is an order entry and the other is a cumulative table. I am inner joining the 2 tables and storing my results in a temp table.
The problem I have with the code is on the inner join where I am mapping based on the cprj columns. I want the count functions to be done on the cumulative table for the cprj and the sum function to be done on the orderentry table for the NoPartsPerLine Column. Something is wrong in the code after the 'ON' section of the inner join.

The answer I seem to be getting is that it counts the rows in the cumulative table and then multiplys by the # of rows for the same cprj that exists in the orderentry table. I tried the select top 1 statement for the orderentry table but it yields no results at all. When (XMLLines.cprj) = PartList.cprj it yields the multiplied result.

The results I am looking for are for example:

cprj TotalRowCount DWRowCount SumNoPartsPerLine CumuCount
TEK#55555 60 8 25 6

The Tables I am using are as such:

[OrderEntry] Table
ID	cprj		TotalRowCount		DWRowCount	NoPartsPerLine
1	TEK#7000	80			26		10
2	TEK#4444	50			30		2
3	TEK#88888	120			24		15
4	TEK#4700	37			13		6
5	TEK#99999	53			42		8
203	TEK#5555	60			8		10
203	TEK#5555	60			8		5
203	TEK#5555	60			8		5
203	TEK#5555	60			8		2
203	TEK#5555	60			8		3

and

[MatchingRowsRUwithRU-DWCALCS-DATA-ProjectCumulative] table
ID	cprj		item		PartNumber		SWITEM	QTY	
196	TEK#7000	A23-3171-X	A23-3171\2583		1.2	2	
197	TEK#5555	N02-0846-X	N02-0846A5\59\2583	2	1	
198	TEK#7000	N02-0846-X	N02-0846A5\59\2583	2	1	
199	TEK#5555	A23-1354-X	A23-1354\2583		2.3	2	
200	TEK#7000	A23-1354-X	A23-1354\2583		2.3	2	
201	TEK#7000	B11-0304-X	B11-0304\25		2.4	2	
202	TEK#7000	B11-0304-X	B11-0304\25		1.4	2	
203	TEK#5555	B11-0304-X	B11-0304\25		2.4	2	
204	TEK#5555	B11-0304-X	B11-0304\25		1.4	2	
205	TEK#5555	B11-0305-X	B11-0305\59		1.5	2	
206	TEK#5555	B11-0305-X	B11-0305\59		2.5	2	
207	TEK#7000	B11-0305-X	B11-0305\59		1.5	2

Any Help is appreciated!

What I have tried:

SQL

DECLARE @tmp TABLE (
    ID INT,
    [cprj] [varchar](500) NULL,
    [TotalRowCount] [int] NULL, 
    [DWRowCount] [int] NULL,
    [SumNoPartsPerLine] [int] NULL,
    [CumuCount] [int] NULL
)

----------------------------------------inner join between order entry and cumulative table and insert results into @tmp table

INSERT INTO @tmp
    (cprj, TotalRowCount, DWRowCount, SumNoPartsPerLine, CumuCount) 
SELECT
    XMLLines.[cprj],
    PartList.TotalRowCount,
    PartList.DWRowCount,
    sum (PartList.NoPartsPerLine) SumNoPartsPerLine,
    count (XMLLines.cprj) CumuCount
FROM 
    OrderEntry.dbo.OrderEntry as PartList
    INNER JOIN SSIS.dbo.[MatchingRowsRUwithRU-DWCALCS-DATA-ProjectCumulative] AS XMLLines 
    ON
        --(XMLLines.cprj) = PartList.cprj
        (Select top 1 cprj from OrderEntry.dbo.OrderEntry) = (XMLLines.cprj)
GROUP BY
    XMLLines.cprj, 
    PartList.DWRowCount, 
    TotalRowCount
;

SELECT * FROM @tmp

Posted 26-Mar-20 7:43am

Member 14778402

Updated 26-Mar-20 8:41am

Richard Deeming

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-03-26T08:03:00

You'll need to do the aggregation before you join the tables.

SQL

DECLARE @tmp TABLE 
(
    ID INT,
    [cprj] [varchar](500) NULL,
    [TotalRowCount] [int] NULL, 
    [DWRowCount] [int] NULL,
    [SumNoPartsPerLine] [int] NULL,
    [CumuCount] [int] NULL
);

WITH ctePartList As
(
    SELECT
        cprj,
        TotalRowCount,
        DWRowCount,
        Sum(NoPartsPerLine) As SumNoPartsPerLine
    FROM
        OrderEntry.dbo.OrderEntry
    GROUP BY
        cprj,
        TotalRowCount,
        DWRowCount
),
cteXmlLines As
(
    SELECT
        cprj,
        Count(1) As CumuCount
    FROM
        SSIS.dbo.[MatchingRowsRUwithRU-DWCALCS-DATA-ProjectCumulative]
    GROUP BY
        cprj
)
INSERT INTO @tmp
(
    cprj, 
    TotalRowCount, 
    DWRowCount, 
    SumNoPartsPerLine, 
    CumuCount
)
SELECT
    P.cprj,
    P.TotalRowCount,
    P.DWRowCount,
    P.SumNoPartsPerLine,
    X.CumuCount
FROM
    ctePartList As P
    INNER JOIN cteXmlLines As X
    ON X.cprj = P.cprj
;

SELECT * FROM @tmp;

k5054 · Accepted Answer · 2020-03-26T08:42:00

I don't think you need the temp table. The following works fine in PostgreSQL. Something similar should work for SQL-Server

SQL

SELECT parts.cprj, TotalRowCount, DWRowCount, SumNoPartsPerLine, cumul.CumuCount
   FROM (SELECT  cprj, TotalRowCount, DWRowCount, sum(NoPartsPerLine) as SumNoPartsPerLine
            FROM OrderEntry
            GROUP BY (cprj, TotalRowCount, DWRowCount) as parts
   INNER JOIN (SELECT cprj, count(cprj) as CumuCount
             FROM MatchingRowsRUwithRU-DWCALCS-DATA-ProjectCumulative
             GROUP BY cprj) as cumul
   ON parts.cprj = cumul.cprj

How do I use the count function on an inner join properly to yield proper results

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0