May 8, 2017 at 7:05 am
I select a list of Txns with counts of TypeA – all good
I select a list of Txns with counts of TypeP – all good
I combine the two select statements based on a main common table and convert counts into Over(Partition By) and drop the Group By and counts then differ…
| TxnID | Total A | Total P | Total A+P | Actual |
| 123456 | 2 | 4 | 6 | 6 |
| 123457 | 2 | 3 | 5 | 6 |
| 123458 | 3 | 4 | 7 | 8 |
| 123459 | 1 | 2 | 3 | 4 |
I know it is in the joins due to even numbers being returned...
Where am I going wrong – how can I get the correct result for total count of TypeA+TypeP
Any assistance much appreciated
Jabba
select R.TxnID, COUNT(A.RAID) as ATotal
from TableR R
inner join TableA A ON R.RID = A.RID and R.TxnID = A.TxnID
group by R.TxnID
select R.TxnID, COUNT(RPI.RPID) as PTotal
from TableR R
inner join TableRP RP ON R.TxnID = RP.TxnID and R.RID = RP.RID
inner join TableRPI RPI ON RP.TxnID = RPI.TxnID and RP.RPID = RPI.RPID
group by R.TxnID
select distinct
R.TxnID, COUNT(A.RAID) over(Partition by R.TxnID) as ATotal
, COUNT(RPI.RPID) over(Partition by R.TxnID) as PTotal
, COUNT(A.RAID) over(Partition by R.TxnID)
+ COUNT(RPI.RPID) over(Partition by R.TxnID) as AplusPTotal
from TableR R
inner join TableA A ON R.RID = A.RID and R.TxnID = A.TxnID
inner join TableRP RP ON R.TxnID = RP.TxnID and R.RID = RP.RID
inner join TableRPI RPI ON RP.TxnID = RPI.TxnID and RP.RPID = RPI.RPID
May 8, 2017 at 7:23 am
I would guess that you you have a one to many relationship somewhere. Thus when doing your COUNT you are counting the same row twice.
Without seeing your data, this is a stab in the dark, but perhaps:
SELECT R.TxnID,
COUNT(DISTINCT A.RAID) AS ATotal
COUNT(DISTINCT RPI.RPID) AS PTotal
COUNT(DISTINCT A.RAID) + COUNT(DISTINCT RPI.RPID) AS AplusPTotal
FROM TableR R
INNER JOIN TableA A ON R.RID = A.RID AND R.TxnID = A.TxnID
INNER JOIN TableRP RP ON R.TxnID = RP.TxnID AND R.RID = RP.RID
INNER JOIN TableRPI RPI ON RP.TxnID = RPI.TxnID AND RP.RPID = RPI.RPID
GROUP BY R.txnID;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 8, 2017 at 7:24 am
The problem here is likely due to not properly understanding the relationships between the tables. If you can accurately describe what a record in each of those tables actually represents, and then after we get a chance to read your description, ask questions which you can then answer, we have a shot at helping. The easiest way to accomplish that is to post DDL (data description language) which includes CREATE TABLE and INSERT statements with sample data, and also include the expected results based on that sample data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 8, 2017 at 7:29 am
Steve is right. DDL and data would make our task much easier. Here's a guess in the absence thereof. Have a look at this and at what Thom posted - one of them might get you started, even if they're not exactly what you're looking for.
WITH AandP AS (
SELECT
RID
, TxnID
, 'A' AS TheTable
FROM TableA
UNION ALL
SELECT
RP.RID
, RP.TxnID
, 'P'
FROM TableRP RP
JOIN TableRPI RPI
ON RP.TxnID = RPI.TxnID AND RP.RID = RPI.RID
)
SELECT
SUM(CASE WHEN TheTable = 'A' THEN 1 ELSE 0 END) AS ATotal
, SUM(CASE WHEN TheTable = 'P' THEN 1 ELSE 0 END) AS PTotal
, COUNT(DISTINCT RID) AS APlusPTotal
FROM TableR r
JOIN AandP ap
ON r.RID = ap.RID AND r.TxnID = ap.TxnID;
John
May 8, 2017 at 7:57 am
I agree with Thom, Steve & John and offer an alternative resolution:SELECT
R.TxnID,
SUM(x1.cnt) AS ATotal,
SUM(x2.cnt) AS PTotal,
SUM(x1.cnt) + SUM(x2.cnt) AS AplusPTotal
FROM TableR R
CROSS APPLY (
SELECT cnt = COUNT(A.RAID)
FROM TableA A
WHERE R.RID = A.RID
AND R.TxnID = A.TxnID
) x1
CROSS APPLY (
SELECT cnt = COUNT(RPI.RPID)
FROM TableRP RP
INNER JOIN TableRPI RPI
ON RP.TxnID = RPI.TxnID AND RP.RPID = RPI.RPID
WHERE R.TxnID = RP.TxnID AND R.RID = RP.RID
) x2
GROUP BY R.TxnID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 8, 2017 at 7:45 pm
This should be easy to understand while doing the trick:
select
R.TxnID
, ISNULL(ATotal, 0)ATotal
, ISNULL(PTotal,0) PTotal
, ISNULL(ATotal, 0) + ISNULL(PTotal,0) as AplusPTotal
from (
SELECT TxnID from TableR GROUP BY TxnID
) R
LEFT join (select TxnID, COUNT(A.RAID) ATotal
from TableA
group by TxnID
) A ON R.TxnID = A.TxnID
LEFT join (
select RP.TxnID, COUNT(RPI.RPID) as PTotal
from TableRP
inner join TableRPI RPI ON RP.TxnID = RPI.TxnID and RP.RPID = RPI.RPID
group by RP.TxnID
) RP ON R.TxnID = RP.TxnID
There is a question though about the role of RID in this query.
I eliminated it because it does not seem to be a part of the logic for this query.
Was I right doing so?
_____________
Code for TallyGenerator
May 9, 2017 at 12:31 am
Thank you to everyone for helping out... I will investigate the replies and mark up the answers in due course
I will also check out Jeffs guide on encapsulating IF SQL Markup - although I did try... and thought I had despite not taking the whole DDL approach... but all points taken and promise to do so going forward.
Finally it continues to amaze me how there is always lots of ways of skinning a cat - errr... so to speak 🙂
Thanks once again for everyone's time
Best regards
Jabba
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply