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      |

Are you creating actual files from these queries? Or just result sets. — HLGEM, Aug 10 '17 at 17:50
@jderekc: You answered the question yourself. You say you are using the `secondary ID` in your 'Detail' query, but you are not using it in your 'Trailer' query. If the `secondary ID` is part of your selection criteria for the 'Detail' query, then logically it follows you will need the `secondary ID` in your 'Trailer' query to get the same count. — abraxascarab, Aug 10 '17 at 18:16
A second thing you can do: If your 'Trailer' query always only returns just 1 record, you can use the tsql function `@@rowcount` right after your 'Detail' query. Then you can just place the actual rowcount into your 'Trailer'. You would need to declare an integer variable, as in: `DECLARE @rc as integer;` Then after your 'Detail' query assign @rc the rowcount: `set @rc = @@rowcount;` Then in your Trailer query you can use the @rc variable where you want your row count. — abraxascarab, Aug 10 '17 at 18:22
These query result sets will be exported to unique files. Also, if you look at my case statement I am trying to include both IDs for comparison but my logic isn't correct. — jderekc, Aug 10 '17 at 18:24
@jderekc: You may need to show us your 'Detail' query so we can see how your `primary ID` and your `secondary ID` are actually being used. If your 'Trailer' query is dropping off your `secondary ID` records, then your logic problem lies there. — abraxascarab, Aug 10 '17 at 18:31
@abraxascarab I've edited my post to show additional information. Thanks so much for your help so far! Let me know if I should add more information. — jderekc, Aug 10 '17 at 18:53

abraxascarab · Accepted Answer · 2017-08-10T19:05:07.277

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 :)

This actually works, thanks! I'm not sure yet how I'm going to export this to multiple files though, but I haven't tried it yet. — jderekc, Aug 10 '17 at 18:59

jderekc · Answer 2 · 2017-08-10T20:50:10.883

I found a way to do this using a derived table so that I can still keep the queries separate and use SSIS to populate separate flat files (couldn't do this with one query having multiple select statements without UNIONs). Thanks to all for helping me get there! Your suggestions were much appreciated.

Here is what I used:

SELECT 
    CAST('TRL' AS VARCHAR(3)) AS RECID,
    COUNT(TotalRows) AS TOTREC 
FROM (SELECT DISTINCT
    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,
    @@ROWCOUNT AS TotalRows,
    I.ITMDESC
    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%')

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

2 Answers2