Post reply

Row with Non-Zero Value

  • August 9, 2018 at 4:02 pm

    #357304

    I have the following table and want to find the row with non-zero value for revenue and quantity for each combination.
    So the result should be three rows as below: Thanks.
    3 a
    2 b
    1 c

    create table #temp
    (
        prodID int
        ,mktID nvarchar(255)
        ,rev decimal
        ,qty decimal
    )

    insert into #temp (prodID, mktID, rev, qty)
    values (1, 'a', 100, 10)
    insert into #temp (prodID, mktID, rev, qty)
    values (2, 'a', 102, 11)
    insert into #temp (prodID, mktID, rev, qty)
    values (3, 'a', 110, 12)
    insert into #temp (prodID, mktID, rev, qty)
    values (1, 'b', 200, 10)
    insert into #temp (prodID, mktID, rev, qty)
    values (2, 'b', 202, 11)
    insert into #temp (prodID, mktID, rev, qty)
    values (3, 'b', 0, 0)
    insert into #temp (prodID, mktID, rev, qty)
    values (1, 'c', 310, 12)
    insert into #temp (prodID, mktID, rev, qty)
    values (2, 'c', 0, 0)
    insert into #temp (prodID, mktID, rev, qty)
    values (3, 'c', 0, 0)

    select * from #temp

  • August 10, 2018 at 12:17 am

    #2001156

    Why would the first result be (3 a), when (1 a) and (2 a) also appear to have non-zero values for revenue and quantity?

  • August 10, 2018 at 1:47 am

    #2001164

    Highest prod Iā€™d.

  • August 10, 2018 at 6:25 am

    #2001190

    create table #temp
    (
        prodID int
        ,mktID nvarchar(255)
        ,rev decimal
        ,qty decimal
    )

    insert into #temp (prodID, mktID, rev, qty)
    values (1, 'a', 100, 10)
    insert into #temp (prodID, mktID, rev, qty)
    values (2, 'a', 102, 11)
    insert into #temp (prodID, mktID, rev, qty)
    values (3, 'a', 110, 12)
    insert into #temp (prodID, mktID, rev, qty)
    values (1, 'b', 200, 10)
    insert into #temp (prodID, mktID, rev, qty)
    values (2, 'b', 202, 11)
    insert into #temp (prodID, mktID, rev, qty)
    values (3, 'b', 0, 0)
    insert into #temp (prodID, mktID, rev, qty)
    values (1, 'c', 310, 12)
    insert into #temp (prodID, mktID, rev, qty)
    values (2, 'c', 0, 0)
    insert into #temp (prodID, mktID, rev, qty)
    values (3, 'c', 0, 0)

    Select prodID, mktID, rev, qty
    from
    (
    select *
            ,row_number() over (partition by mktID order by prodID desc ) as rk
    from #temp
    where isnull(rev,0) <> 0  and isnull(qty,0) <> 0
    ) as a
    where rk =1

  • August 10, 2018 at 6:31 am

    #2001192

    DROP TABLE IF EXISTS #temp;

    CREATE TABLE #temp
    (
      prodID INT,
      mktID NVARCHAR(255),
      rev DECIMAL,
      qty DECIMAL
    );

    INSERT #temp
    (
      prodID,
      mktID,
      rev,
      qty
    )
    VALUES
    (1, 'a', 100, 10),
    (2, 'a', 102, 11),
    (3, 'a', 110, 12),
    (1, 'b', 200, 10),
    (2, 'b', 202, 11),
    (3, 'b', 0, 0),
    (1, 'c', 310, 12),
    (2, 'c', 0, 0),
    (3, 'c', 0, 0);

    WITH ordered
    AS (SELECT *,
        ro = ROW_NUMBER() OVER (PARTITION BY prodID ORDER BY mktID DESC)
      FROM #temp
      WHERE rev <> 0
        AND qty <> 0)
    SELECT *
    FROM ordered
    WHERE ordered.ro = 1;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 14, 2018 at 6:33 am

    #2001623

    Just seems to me to be a simple group by:
    CREATE TABLE #temp (
        prodID int,
        mktID nvarchar(255),
        rev decimal,
        qty decimal,
        UNIQUE CLUSTERED
            (
            mktID ASC,
            prodID ASC
            )
    );
    INSERT INTO #temp (prodID, mktID, rev, qty)
        VALUES    (1, 'a', 100, 10),
                (2, 'a', 102, 11),
                (3, 'a', 110, 12),
                (1, 'b', 200, 10),
                (2, 'b', 202, 11),
                (3, 'b', 0, 0),
                (1, 'c', 310, 12),
                (2, 'c', 0, 0),
                (3, 'c', 0, 0);

    SELECT
        COUNT(T.prodID) AS [count],
        T.mktID
    FROM #temp AS T
    WHERE    T.qty <> 0
        AND T.rev <> 0
    GROUP BY T.mktID
    ORDER BY COUNT(T.prodID) DESC;

    DROP TABLE #temp;

    I chose to add an appropriate clustered index based solely on this query, so take that for what it's worth.   Just didn't see a need to add a row number when GROUP BY works just fine.

    Steve (aka sgmunson) šŸ™‚ šŸ™‚ šŸ™‚
    Rent Servers for Income (picks and shovels strategy)

  • August 15, 2018 at 5:37 am

    #2001821

    Maybe I'm reading the OP's (clarified) requirements wrong.

    But I don't think he wants a count of the number of non-zero values.  Rather, he wants the highest prod id with non-zero values.

    So maybe MAX instead of COUNT?

  • August 15, 2018 at 5:49 am

    #2001823

    gvoshol 73146 - Wednesday, August 15, 2018 5:37 AM

    Maybe I'm reading the OP's (clarified) requirements wrong.

    But I don't think he wants a count of the number of non-zero values.  Rather, he wants the highest prod id with non-zero values.

    So maybe MAX instead of COUNT?

    select MAX(ProdId) ProdId, mktId
    from #temp
    where rev <> 0
    and qty <>0
    group by mktId
    order by 1 desc

  • August 15, 2018 at 7:04 am

    #2001846

    gvoshol 73146 - Wednesday, August 15, 2018 5:37 AM

    Maybe I'm reading the OP's (clarified) requirements wrong.

    But I don't think he wants a count of the number of non-zero values.  Rather, he wants the highest prod id with non-zero values.

    So maybe MAX instead of COUNT?

    The OP specified the desired results up front.

    Steve (aka sgmunson) šŸ™‚ šŸ™‚ šŸ™‚
    Rent Servers for Income (picks and shovels strategy)

  • August 15, 2018 at 7:12 am

    #2001851

    sgmunson - Wednesday, August 15, 2018 7:04 AM

    gvoshol 73146 - Wednesday, August 15, 2018 5:37 AM

    Maybe I'm reading the OP's (clarified) requirements wrong.

    But I don't think he wants a count of the number of non-zero values.  Rather, he wants the highest prod id with non-zero values.

    So maybe MAX instead of COUNT?

    The OP specified the desired results up front.

    Yes, they said: "Highest prod I’d" not count

  • August 15, 2018 at 7:52 am

    #2001867

    Jonathan AC Roberts - Wednesday, August 15, 2018 7:12 AM

    sgmunson - Wednesday, August 15, 2018 7:04 AM

    gvoshol 73146 - Wednesday, August 15, 2018 5:37 AM

    Maybe I'm reading the OP's (clarified) requirements wrong.

    But I don't think he wants a count of the number of non-zero values.  Rather, he wants the highest prod id with non-zero values.

    So maybe MAX instead of COUNT?

    The OP specified the desired results up front.

    Yes, they said: "Highest prod I’d" not count

    Ahhhh, yes...   I do see that post now....  Interesting that the data just happens to also coincide with the COUNT values for the number of prodID's that have non-zero revenue and quantity values.   Not sure the OP is still looking at this thread, so I'll propose a question:  What would the results be if the data included an additional mktID value?
    CREATE TABLE #temp (
      prodID int,
      mktID nvarchar(255),
      rev decimal,
      qty decimal,
      UNIQUE CLUSTERED
       (
       mktID ASC,
       prodID ASC
       )
    );
    INSERT INTO #temp (prodID, mktID, rev, qty)
      VALUES (1, 'a', 100, 10),
        (2, 'a', 102, 11),
        (3, 'a', 110, 12),
        (1, 'b', 200, 10),
        (2, 'b', 202, 11),
        (3, 'b', 0, 0),
        (1, 'c', 310, 12),
        (2, 'c', 0, 0),
        (3, 'c', 0, 0),
                (1, 'd', 0, 0),
        (2, 'd', 202, 21),
        (3, 'd', 210, 22);
        
    SELECT
      COUNT(T.prodID) AS [count],    --prodID,
      T.mktID
    FROM #temp AS T
    WHERE T.qty <> 0
      AND T.rev <> 0
    GROUP BY T.mktID
    ORDER BY COUNT(T.prodID) DESC;

    SELECT
      MAX(T.prodID) AS prodID,
      T.mktID
    FROM #temp AS T
    WHERE T.qty <> 0
      AND T.rev <> 0
    GROUP BY T.mktID
    ORDER BY MAX(T.prodID) DESC;

    DROP TABLE #temp;
    /*
    COUNT RESULTS:
    =================
    count    mktID
    ----- -----
    3   a
    2   b
    2   d
    1   c

    MAX RESULTS:
    =================
    prodID    mktID
    ------ -----
     3  a
     3  d
     2  b
     1  c
    */

    Had to play with the data a bit to make a scenario that differed between the two methods.   Now if we could just interest the original poster in this question...

    Steve (aka sgmunson) šŸ™‚ šŸ™‚ šŸ™‚
    Rent Servers for Income (picks and shovels strategy)

  • August 15, 2018 at 8:12 am

    #2001882

    sgmunson - Wednesday, August 15, 2018 7:52 AM

    Jonathan AC Roberts - Wednesday, August 15, 2018 7:12 AM

    sgmunson - Wednesday, August 15, 2018 7:04 AM

    gvoshol 73146 - Wednesday, August 15, 2018 5:37 AM

    Maybe I'm reading the OP's (clarified) requirements wrong.

    But I don't think he wants a count of the number of non-zero values.  Rather, he wants the highest prod id with non-zero values.

    So maybe MAX instead of COUNT?

    The OP specified the desired results up front.

    Yes, they said: "Highest prod I’d" not count

    Ahhhh, yes...   I do see that post now....  Interesting that the data just happens to also coincide with the COUNT values for the number of prodID's that have non-zero revenue and quantity values.   Not sure the OP is still looking at this thread, so I'll propose a question:  What would the results be if the data included an additional mktID value?
    CREATE TABLE #temp (
      prodID int,
      mktID nvarchar(255),
      rev decimal,
      qty decimal,
      UNIQUE CLUSTERED
       (
       mktID ASC,
       prodID ASC
       )
    );
    INSERT INTO #temp (prodID, mktID, rev, qty)
      VALUES (1, 'a', 100, 10),
        (2, 'a', 102, 11),
        (3, 'a', 110, 12),
        (1, 'b', 200, 10),
        (2, 'b', 202, 11),
        (3, 'b', 0, 0),
        (1, 'c', 310, 12),
        (2, 'c', 0, 0),
        (3, 'c', 0, 0),
                (1, 'd', 0, 0),
        (2, 'd', 202, 21),
        (3, 'd', 210, 22);
        
    SELECT
      COUNT(T.prodID) AS [count],    --prodID,
      T.mktID
    FROM #temp AS T
    WHERE T.qty <> 0
      AND T.rev <> 0
    GROUP BY T.mktID
    ORDER BY COUNT(T.prodID) DESC;

    SELECT
      MAX(T.prodID) AS prodID,
      T.mktID
    FROM #temp AS T
    WHERE T.qty <> 0
      AND T.rev <> 0
    GROUP BY T.mktID
    ORDER BY MAX(T.prodID) DESC;

    DROP TABLE #temp;
    /*
    COUNT RESULTS:
    =================
    count    mktID
    ----- -----
    3   a
    2   b
    2   d
    1   c

    MAX RESULTS:
    =================
    prodID    mktID
    ------ -----
     3  a
     3  d
     2  b
     1  c
    */

    Had to play with the data a bit to make a scenario that differed between the two methods.   Now if we could just interest the original poster in this question...

    Hehehe: Also, see here: my answer

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply