Two separate queries; one needs to count rows from the other

Question

I have two separate queries. One I am using for a details file and the other I am using for the end/trailer file (I also have a header file but that one is irrelevant to this question). I will have to keep these separate as they will return different columns.

The problem is my detail file has 13,470 rows but when I do a count in the secondary query (the trailer file), I get 13,207 rows. The reason is that a few items have multiple secondary IDs associated with the primary item ID.

I can get the same count if I don't use a SELECT DISTINCT, but it then returns 25,250 rows. I need to keep duplicates out of my details file.

The details query is quite long, but just understand that even though ALMOST all records are unique, there are some where the primary item ID is seemingly duplicated only because the secondary item ID might have a few different values for the primary item ID.

I've read the following articles but can't seem to get any of it to work. Note that I am using Microsoft SQL Server 2012 and not MySQL, but I did apply the concept of MySQL to my needs that was explained in one of the articles:

Multiple COUNT() for multiple conditions in one query (MySQL)

SUM of grouped COUNT in SQL Query

Counting Values based on distinct values from another Column

So again, I would like to get a count based on all of the criteria I specified in the details file (dozens of columns and 13,470 rows). My trailer file is only two columns and one row. One to identify that it is the end/trailer file and the other to show the count of records that should have been returned by the details file.

Here's my "just try and see if this works or is on the right path" query (and it doesn't):

    SELECT DISTINCT
    CAST('TRL' AS VARCHAR(3)) AS RECID,
    (CASE 
        WHEN COUNT(I2.VNDRITNM) > COUNT(DISTINCT I2.ITEMNMBR)
        THEN COUNT(I2.VNDRITNM)
        WHEN COUNT(I2.VNDRITNM) = COUNT(DISTINCT I2.ITEMNMBR)
        THEN COUNT(I2.ITEMNMBR)
        ELSE COUNT(DISTINCT I2.ITEMNMBR)
    END) AS TOTREC
    FROM Inv00101 I
    JOIN Inv00102 I2 ON I2.ITEMNMBR = I.ITEMNMBR
    JOIN ItemUnit I3 ON I3.ITEMNMBR = I.ITEMNMBR
    LEFT OUTER JOIN prodmaster D ON D.itemid = I.ITEMNMBR
    LEFT OUTER JOIN productinfo I4 ON I4.ITEMNMBR = I.ITEMNMBR
    WHERE (ITMDESC LIKE '%ART%' OR ITMDESC LIKE '%CRAFT%')

This returns 25,250 rows.

Of course the CASE statement is wrong, but I just wanted to explore that as an option. Does anyone have any idea on how I can get my queries to sync up?

Again:

Microsoft SQL Server 2012
1 Header File (works)
1 Details File (works)
1 Trailer File (not working as intended)

Trailer results using CASE statement:

  | RECID | TOTREC |
--------------------
1 | TRL   | 25250  |

Trailer results just counting DISTINCT on one of the columns:

  | RECID | TOTREC |
--------------------
1 | TRL   | 13207  |

Looking for:

  | RECID | TOTREC |
--------------------
1 | TRL   | 13470  |

Any advice would be greatly appreciated. Thanks!

EDIT

Here is the Detail file query, but I've removed irrelevant columns; I executed this query and it works the same as the non-edited query, so this should be good enough to tell:

SELECT DISTINCT
    RTRIM(CAST('DTL' AS VARCHAR(3))) AS RECID,
    RTRIM(CAST('12345' AS VARCHAR(10))) AS COMPANY,
    RTRIM(CAST(CASE 
                WHEN I2.VNDITNUM = ''
                THEN 'BLANK'
                ELSE I2.VNDITNUM END AS VARCHAR(20))) AS VNDITEM,
    RTRIM(CAST(I.ITEMNMBR AS VARCHAR(20))) AS NUMITEM,
    RTRIM(CAST(I.ITEMDESC AS VARCHAR(60))) AS ITMDESC,
    RTRIM(CAST(CASE 
          WHEN I.INACTIVE = '0' 
          THEN 'A' 
          WHEN I.INACTIVE = '1' 
          THEN 'I' END AS VARCHAR(1))) AS STATUS
FROM Inv00101 I
JOIN Inv00102 I2 ON I2.ITEMNMBR = I.ITEMNMBR
JOIN ItemUnit I3 ON I3.ITEMNMBR = I.ITEMNMBR
LEFT OUTER JOIN prodmaster D ON D.itemid = I.ITEMNMBR
LEFT OUTER JOIN productinfo I4 ON I4.ITEMNMBR = I.ITEMNMBR
WHERE (ITMDESC LIKE '%ART%' OR ITMDESC LIKE '%CRAFT%')

Note that ITEMNMBR is the primary, internal ID whereas VNDITNUM is the vendor/supplier ID, which I call the secondary ID. It is VNDITNUM that sometimes has more than one record for a unique primary, internal ID of a product.

A normal result would be as follows:

  | RECID | COMPANY | VNDITEM | NUMITEM | ITMDESC | STATUS |
------------------------------------------------------------
1 | DTL   | 12345   | 011223  | 100234  | Game    | A      |
2 | DTL   | 12345   | 015992  | 104722  | Picture | A      |

But here is an example as how it might duplicate:

  | RECID | COMPANY | VNDITEM | NUMITEM | ITMDESC | STATUS |
------------------------------------------------------------
1 | DTL   | 12345   | 029445  | 109777  | Book A  | A      |
2 | DTL   | 12345   | 029478  | 109777  | Book A  | A      |

abraxascarab · Accepted Answer

If your 'Trailer' query only ever returns 1 record... then you can try to use the tsql function @@recordcount

Like this:

-- Declare an integer variable
DECLARE @rc as integer;

-- Your Detail query
SELECT
   IDont
   ,CareWhat
   ,TheDetailLogicIs
FROM
   Inv00101 as i
   JOIN WhoKnowsWhat as idk ON i.ITEMNMBR = idk.ITEMNMBR
WHERE 
   Who = Cares;

-- Assign your row count variable with the row count of the last executed query.
SET @rc = @@rowcount;

-- Your new Trailer query
SELECT
   'TRL' AS [RECID]
   ,@rc AS [TOTREC];

This has the added benefit of giving you the row count of the actual query you are interested in having the count of. Plus you don't have to execute duplicate logic which could be time consuming (especially if your Detail query is as complicated as you make it out to be).

Hope that helps :)

Two separate queries; one needs to count rows from the other

Answers (2)

Related Questions