August 9, 2018 at 4:02 pm
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
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
Highest prod Iād.
August 10, 2018 at 6:25 am
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
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 14, 2018 at 6:33 am
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
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
gvoshol 73146 - Wednesday, August 15, 2018 5:37 AMMaybe 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
gvoshol 73146 - Wednesday, August 15, 2018 5:37 AMMaybe 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
sgmunson - Wednesday, August 15, 2018 7:04 AMgvoshol 73146 - Wednesday, August 15, 2018 5:37 AMMaybe 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
Jonathan AC Roberts - Wednesday, August 15, 2018 7:12 AMsgmunson - Wednesday, August 15, 2018 7:04 AMgvoshol 73146 - Wednesday, August 15, 2018 5:37 AMMaybe 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
sgmunson - Wednesday, August 15, 2018 7:52 AMJonathan AC Roberts - Wednesday, August 15, 2018 7:12 AMsgmunson - Wednesday, August 15, 2018 7:04 AMgvoshol 73146 - Wednesday, August 15, 2018 5:37 AMMaybe 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 cMAX 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