Row with Non-Zero Value

• tinausa

  SSCrazy

  Points: 2697

  More actions

  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

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  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?

• tinausa

  SSCrazy

  Points: 2697

  More actions

  August 10, 2018 at 1:47 am

  #2001164

  Highest prod I’d.

• arvind.patil 60171

  SSC Enthusiast

  Points: 174

  More actions

  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

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  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

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  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)

• GaryV

  Hall of Fame

  Points: 3307

  More actions

  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?

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  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

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  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)

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  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

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  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)

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  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