Mixing
SQL Server : please confirm if my query provides correct results
-5
I want to show which orders contain more than two products?
Show order number, order value, and number of products that the order contains.
FYI I added the 2 extra columns because the result would look better.
WITH RESULTS AS
(
SELECT
*,
COUNT(O2.SalesOrderID) OVER (PARTITION BY O2.SalesOrderID) AS COUNTS
FROM
AdventureWorks2014.Sales.SalesOrderDetail AS O2
-- WHERE COUNTS > 1
-- ORDER BY SalesOrderID ASC
)
SELECT
O.SalesOrderID,
p.Name, P.ProductID, R.COUNTS as [Products Amount],
R.LineTotal, R.OrderQty
FROM
RESULTS AS R
INNER JOIN
AdventureWorks2014.Sales.SalesOrderDetail AS O ON R.SalesOrderID = O.SalesOrderID
RIGHT JOIN
AdventureWorks2014.Production.Product as P ON P.ProductID = R.ProductID
WHERE
R.COUNTS > 1
ORDER BY
O.SalesOrderID ASC
CLICK HERE TO SEE THE NEW IMAGE PLEASE
sql-server asked Jan 2 at 3:41
Zabi SidiqkhilZabi Sidiqkhil
846
add a comment |
-5
I want to show which orders contain more than two products?
Show order number, order value, and number of products that the order contains.
FYI I added the 2 extra columns because the result would look better.
WITH RESULTS AS
(
SELECT
*,
COUNT(O2.SalesOrderID) OVER (PARTITION BY O2.SalesOrderID) AS COUNTS
FROM
AdventureWorks2014.Sales.SalesOrderDetail AS O2
-- WHERE COUNTS > 1
-- ORDER BY SalesOrderID ASC
)
SELECT
O.SalesOrderID,
p.Name, P.ProductID, R.COUNTS as [Products Amount],
R.LineTotal, R.OrderQty
FROM
RESULTS AS R
INNER JOIN
AdventureWorks2014.Sales.SalesOrderDetail AS O ON R.SalesOrderID = O.SalesOrderID
RIGHT JOIN
AdventureWorks2014.Production.Product as P ON P.ProductID = R.ProductID
WHERE
R.COUNTS > 1
ORDER BY
O.SalesOrderID ASC
CLICK HERE TO SEE THE NEW IMAGE PLEASE
sql-server asked Jan 2 at 3:41
Zabi SidiqkhilZabi Sidiqkhil
846
Please provide a Minimal, Complete, and Verifiable Example (MCVE) meta.stackoverflow.com/questions/333952/…
– Dale Burrell
Jan 2 at 4:02
1
SQL Server returns the correct result for the data and query. If it returns something unexpected, it is a problem with the query and/or data assumptions. There is nothing to ‘confirm’ on SO.
– user2864740
Jan 2 at 4:09
Hi guys, I have added an image to the query which shows the problems when I add joins
– Zabi Sidiqkhil
Jan 2 at 13:23
I got it right, i moved the joins to the RESULTS query which includes counts, then put a where clause on COUNTS and job done
– Zabi Sidiqkhil
Jan 2 at 19:13
add a comment |
-5
-5
-5
I want to show which orders contain more than two products?
Show order number, order value, and number of products that the order contains.
FYI I added the 2 extra columns because the result would look better.
WITH RESULTS AS
(
SELECT
*,
COUNT(O2.SalesOrderID) OVER (PARTITION BY O2.SalesOrderID) AS COUNTS
FROM
AdventureWorks2014.Sales.SalesOrderDetail AS O2
-- WHERE COUNTS > 1
-- ORDER BY SalesOrderID ASC
)
SELECT
O.SalesOrderID,
p.Name, P.ProductID, R.COUNTS as [Products Amount],
R.LineTotal, R.OrderQty
FROM
RESULTS AS R
INNER JOIN
AdventureWorks2014.Sales.SalesOrderDetail AS O ON R.SalesOrderID = O.SalesOrderID
RIGHT JOIN
AdventureWorks2014.Production.Product as P ON P.ProductID = R.ProductID
WHERE
R.COUNTS > 1
ORDER BY
O.SalesOrderID ASC
CLICK HERE TO SEE THE NEW IMAGE PLEASE
sql-server asked Jan 2 at 3:41
Zabi SidiqkhilZabi Sidiqkhil
846
I want to show which orders contain more than two products?
Show order number, order value, and number of products that the order contains.
FYI I added the 2 extra columns because the result would look better.
WITH RESULTS AS
(
SELECT
*,
COUNT(O2.SalesOrderID) OVER (PARTITION BY O2.SalesOrderID) AS COUNTS
FROM
AdventureWorks2014.Sales.SalesOrderDetail AS O2
-- WHERE COUNTS > 1
-- ORDER BY SalesOrderID ASC
)
SELECT
O.SalesOrderID,
p.Name, P.ProductID, R.COUNTS as [Products Amount],
R.LineTotal, R.OrderQty
FROM
RESULTS AS R
INNER JOIN
AdventureWorks2014.Sales.SalesOrderDetail AS O ON R.SalesOrderID = O.SalesOrderID
RIGHT JOIN
AdventureWorks2014.Production.Product as P ON P.ProductID = R.ProductID
WHERE
R.COUNTS > 1
ORDER BY
O.SalesOrderID ASC
CLICK HERE TO SEE THE NEW IMAGE PLEASE
sql-server sql-server asked Jan 2 at 3:41
Zabi SidiqkhilZabi Sidiqkhil
846
asked Jan 2 at 3:41
Zabi SidiqkhilZabi Sidiqkhil
846
edited Jan 2 at 13:22
Zabi Sidiqkhil
asked Jan 2 at 3:41
Zabi SidiqkhilZabi Sidiqkhil
846
asked Jan 2 at 3:41
Zabi SidiqkhilZabi Sidiqkhil
846
asked Jan 2 at 3:41
Zabi SidiqkhilZabi Sidiqkhil
846
846
Please provide a Minimal, Complete, and Verifiable Example (MCVE) meta.stackoverflow.com/questions/333952/…
– Dale Burrell
Jan 2 at 4:02
1
SQL Server returns the correct result for the data and query. If it returns something unexpected, it is a problem with the query and/or data assumptions. There is nothing to ‘confirm’ on SO.
– user2864740
Jan 2 at 4:09
Hi guys, I have added an image to the query which shows the problems when I add joins
– Zabi Sidiqkhil
Jan 2 at 13:23
I got it right, i moved the joins to the RESULTS query which includes counts, then put a where clause on COUNTS and job done
– Zabi Sidiqkhil
Jan 2 at 19:13
add a comment |
Please provide a Minimal, Complete, and Verifiable Example (MCVE) meta.stackoverflow.com/questions/333952/…
– Dale Burrell
Jan 2 at 4:02
1
SQL Server returns the correct result for the data and query. If it returns something unexpected, it is a problem with the query and/or data assumptions. There is nothing to ‘confirm’ on SO.
– user2864740
Jan 2 at 4:09
Hi guys, I have added an image to the query which shows the problems when I add joins
– Zabi Sidiqkhil
Jan 2 at 13:23
I got it right, i moved the joins to the RESULTS query which includes counts, then put a where clause on COUNTS and job done
– Zabi Sidiqkhil
Jan 2 at 19:13
Please provide a Minimal, Complete, and Verifiable Example (MCVE) meta.stackoverflow.com/questions/333952/…
– Dale Burrell
Jan 2 at 4:02
Please provide a Minimal, Complete, and Verifiable Example (MCVE) meta.stackoverflow.com/questions/333952/…
– Dale Burrell
Jan 2 at 4:02
1
1
SQL Server returns the correct result for the data and query. If it returns something unexpected, it is a problem with the query and/or data assumptions. There is nothing to ‘confirm’ on SO.
– user2864740
Jan 2 at 4:09
SQL Server returns the correct result for the data and query. If it returns something unexpected, it is a problem with the query and/or data assumptions. There is nothing to ‘confirm’ on SO.
– user2864740
Jan 2 at 4:09
Hi guys, I have added an image to the query which shows the problems when I add joins
– Zabi Sidiqkhil
Jan 2 at 13:23
Hi guys, I have added an image to the query which shows the problems when I add joins
– Zabi Sidiqkhil
Jan 2 at 13:23
I got it right, i moved the joins to the RESULTS query which includes counts, then put a where clause on COUNTS and job done
– Zabi Sidiqkhil
Jan 2 at 19:13
I got it right, i moved the joins to the RESULTS query which includes counts, then put a where clause on COUNTS and job done
– Zabi Sidiqkhil
Jan 2 at 19:13
add a comment |
1 Answer
1
active
oldest
votes
0
I got it right, i moved the joins to the RESULTS query which includes counts, then put a where clause on COUNTS and job done
WITH RESULTS AS
(
SELECT O2.SalesOrderID
,O2.ProductID
,P.Name AS [Product Name]
,O2.UnitPrice
,O2.OrderQty
,O2.LineTotal
,COUNT(O2.SalesOrderID)OVER(PARTITION BY O2.SalesOrderID) AS COUNTS
FROM AdventureWorks2014.Sales.SalesOrderDetail AS O2
INNER JOIN AdventureWorks2014.Sales.SalesOrderHeader AS H ON O2.SalesOrderID = H.SalesOrderID
INNER JOIN AdventureWorks2014.Production.Product as P ON P.ProductID = O2.ProductID
)
SELECT R.SalesOrderID
,R.[Product Name]
,R.ProductID
,R.COUNTS AS [Products Amount Per Order]
,R.OrderQty
,R.UnitPrice
,R.LineTotal AS [Total Payment Per Order]
FROM RESULTS AS R
WHERE R.COUNTS > 1
ORDER BY SalesOrderID ASC
answered Jan 2 at 19:16
Zabi SidiqkhilZabi Sidiqkhil
846
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54000968%2fsql-server-please-confirm-if-my-query-provides-correct-results%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
I got it right, i moved the joins to the RESULTS query which includes counts, then put a where clause on COUNTS and job done
WITH RESULTS AS
(
SELECT O2.SalesOrderID
,O2.ProductID
,P.Name AS [Product Name]
,O2.UnitPrice
,O2.OrderQty
,O2.LineTotal
,COUNT(O2.SalesOrderID)OVER(PARTITION BY O2.SalesOrderID) AS COUNTS
FROM AdventureWorks2014.Sales.SalesOrderDetail AS O2
INNER JOIN AdventureWorks2014.Sales.SalesOrderHeader AS H ON O2.SalesOrderID = H.SalesOrderID
INNER JOIN AdventureWorks2014.Production.Product as P ON P.ProductID = O2.ProductID
)
SELECT R.SalesOrderID
,R.[Product Name]
,R.ProductID
,R.COUNTS AS [Products Amount Per Order]
,R.OrderQty
,R.UnitPrice
,R.LineTotal AS [Total Payment Per Order]
FROM RESULTS AS R
WHERE R.COUNTS > 1
ORDER BY SalesOrderID ASC
answered Jan 2 at 19:16
Zabi SidiqkhilZabi Sidiqkhil
846
add a comment |
0
I got it right, i moved the joins to the RESULTS query which includes counts, then put a where clause on COUNTS and job done
WITH RESULTS AS
(
SELECT O2.SalesOrderID
,O2.ProductID
,P.Name AS [Product Name]
,O2.UnitPrice
,O2.OrderQty
,O2.LineTotal
,COUNT(O2.SalesOrderID)OVER(PARTITION BY O2.SalesOrderID) AS COUNTS
FROM AdventureWorks2014.Sales.SalesOrderDetail AS O2
INNER JOIN AdventureWorks2014.Sales.SalesOrderHeader AS H ON O2.SalesOrderID = H.SalesOrderID
INNER JOIN AdventureWorks2014.Production.Product as P ON P.ProductID = O2.ProductID
)
SELECT R.SalesOrderID
,R.[Product Name]
,R.ProductID
,R.COUNTS AS [Products Amount Per Order]
,R.OrderQty
,R.UnitPrice
,R.LineTotal AS [Total Payment Per Order]
FROM RESULTS AS R
WHERE R.COUNTS > 1
ORDER BY SalesOrderID ASC
answered Jan 2 at 19:16
Zabi SidiqkhilZabi Sidiqkhil
846
add a comment |
0
0
0
I got it right, i moved the joins to the RESULTS query which includes counts, then put a where clause on COUNTS and job done
WITH RESULTS AS
(
SELECT O2.SalesOrderID
,O2.ProductID
,P.Name AS [Product Name]
,O2.UnitPrice
,O2.OrderQty
,O2.LineTotal
,COUNT(O2.SalesOrderID)OVER(PARTITION BY O2.SalesOrderID) AS COUNTS
FROM AdventureWorks2014.Sales.SalesOrderDetail AS O2
INNER JOIN AdventureWorks2014.Sales.SalesOrderHeader AS H ON O2.SalesOrderID = H.SalesOrderID
INNER JOIN AdventureWorks2014.Production.Product as P ON P.ProductID = O2.ProductID
)
SELECT R.SalesOrderID
,R.[Product Name]
,R.ProductID
,R.COUNTS AS [Products Amount Per Order]
,R.OrderQty
,R.UnitPrice
,R.LineTotal AS [Total Payment Per Order]
FROM RESULTS AS R
WHERE R.COUNTS > 1
ORDER BY SalesOrderID ASC
answered Jan 2 at 19:16
Zabi SidiqkhilZabi Sidiqkhil
846
I got it right, i moved the joins to the RESULTS query which includes counts, then put a where clause on COUNTS and job done
WITH RESULTS AS
(
SELECT O2.SalesOrderID
,O2.ProductID
,P.Name AS [Product Name]
,O2.UnitPrice
,O2.OrderQty
,O2.LineTotal
,COUNT(O2.SalesOrderID)OVER(PARTITION BY O2.SalesOrderID) AS COUNTS
FROM AdventureWorks2014.Sales.SalesOrderDetail AS O2
INNER JOIN AdventureWorks2014.Sales.SalesOrderHeader AS H ON O2.SalesOrderID = H.SalesOrderID
INNER JOIN AdventureWorks2014.Production.Product as P ON P.ProductID = O2.ProductID
)
SELECT R.SalesOrderID
,R.[Product Name]
,R.ProductID
,R.COUNTS AS [Products Amount Per Order]
,R.OrderQty
,R.UnitPrice
,R.LineTotal AS [Total Payment Per Order]
FROM RESULTS AS R
WHERE R.COUNTS > 1
ORDER BY SalesOrderID ASC
answered Jan 2 at 19:16
Zabi SidiqkhilZabi Sidiqkhil
846
answered Jan 2 at 19:16
Zabi SidiqkhilZabi Sidiqkhil
846
answered Jan 2 at 19:16
Zabi SidiqkhilZabi Sidiqkhil
846
answered Jan 2 at 19:16
Zabi SidiqkhilZabi Sidiqkhil
846
846
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54000968%2fsql-server-please-confirm-if-my-query-provides-correct-results%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please provide a Minimal, Complete, and Verifiable Example (MCVE) meta.stackoverflow.com/questions/333952/…
– Dale Burrell
Jan 2 at 4:02
1
SQL Server returns the correct result for the data and query. If it returns something unexpected, it is a problem with the query and/or data assumptions. There is nothing to ‘confirm’ on SO.
– user2864740
Jan 2 at 4:09
Hi guys, I have added an image to the query which shows the problems when I add joins
– Zabi Sidiqkhil
Jan 2 at 13:23
I got it right, i moved the joins to the RESULTS query which includes counts, then put a where clause on COUNTS and job done
– Zabi Sidiqkhil
Jan 2 at 19:13