Mixing
Adventure Works 2014 SQL Queries
0
I am writing a SQL query using Adventure Works 2014 database.
I want to show all customers and how many orders each customers have.
I tried to write each select statement by itself (see below), but I'd like to be able to combine both queries into one.
select s.CustomerID ,p.FirstName +' '+p.LastName as Name
from Sales.Customer s,Person.Person p
where s.CustomerID=p.BusinessEntityID
order by s.CustomerID
select CustomerID ,count(SalesOrderID) as OrdersCount
from Sales.SalesOrderHeader
group by CustomerID
mysql sql adventureworks
edited Oct 30 '17 at 23:09
mickmackusa
23.3k103658
asked Oct 30 '17 at 21:29
George KoukiGeorge Kouki
73
add a comment |
0
I am writing a SQL query using Adventure Works 2014 database.
I want to show all customers and how many orders each customers have.
I tried to write each select statement by itself (see below), but I'd like to be able to combine both queries into one.
select s.CustomerID ,p.FirstName +' '+p.LastName as Name
from Sales.Customer s,Person.Person p
where s.CustomerID=p.BusinessEntityID
order by s.CustomerID
select CustomerID ,count(SalesOrderID) as OrdersCount
from Sales.SalesOrderHeader
group by CustomerID
mysql sql adventureworks
edited Oct 30 '17 at 23:09
mickmackusa
23.3k103658
asked Oct 30 '17 at 21:29
George KoukiGeorge Kouki
73
Promote the use of explictJOINsintaxis, Aaron Bertrand wrote a nice article Bad habits to kick : using old-style JOINs about it.
– Juan Carlos Oropeza
Oct 30 '17 at 21:33
Possible duplicate of Northwind SQL total orders per customer
– r2d2oid
Oct 30 '17 at 21:36
add a comment |
0
0
0
I am writing a SQL query using Adventure Works 2014 database.
I want to show all customers and how many orders each customers have.
I tried to write each select statement by itself (see below), but I'd like to be able to combine both queries into one.
select s.CustomerID ,p.FirstName +' '+p.LastName as Name
from Sales.Customer s,Person.Person p
where s.CustomerID=p.BusinessEntityID
order by s.CustomerID
select CustomerID ,count(SalesOrderID) as OrdersCount
from Sales.SalesOrderHeader
group by CustomerID
mysql sql adventureworks
edited Oct 30 '17 at 23:09
mickmackusa
23.3k103658
asked Oct 30 '17 at 21:29
George KoukiGeorge Kouki
73
I am writing a SQL query using Adventure Works 2014 database.
I want to show all customers and how many orders each customers have.
I tried to write each select statement by itself (see below), but I'd like to be able to combine both queries into one.
select s.CustomerID ,p.FirstName +' '+p.LastName as Name
from Sales.Customer s,Person.Person p
where s.CustomerID=p.BusinessEntityID
order by s.CustomerID
select CustomerID ,count(SalesOrderID) as OrdersCount
from Sales.SalesOrderHeader
group by CustomerID
mysql sql adventureworks
mysql sql adventureworks
edited Oct 30 '17 at 23:09
mickmackusa
23.3k103658
asked Oct 30 '17 at 21:29
George KoukiGeorge Kouki
73
edited Oct 30 '17 at 23:09
mickmackusa
23.3k103658
asked Oct 30 '17 at 21:29
George KoukiGeorge Kouki
73
edited Oct 30 '17 at 23:09
mickmackusa
23.3k103658
edited Oct 30 '17 at 23:09
mickmackusa
23.3k103658
edited Oct 30 '17 at 23:09
mickmackusa
23.3k103658
23.3k103658
asked Oct 30 '17 at 21:29
George KoukiGeorge Kouki
73
asked Oct 30 '17 at 21:29
George KoukiGeorge Kouki
73
asked Oct 30 '17 at 21:29
George KoukiGeorge Kouki
73
73
Promote the use of explictJOINsintaxis, Aaron Bertrand wrote a nice article Bad habits to kick : using old-style JOINs about it.
– Juan Carlos Oropeza
Oct 30 '17 at 21:33
Possible duplicate of Northwind SQL total orders per customer
– r2d2oid
Oct 30 '17 at 21:36
add a comment |
Promote the use of explictJOINsintaxis, Aaron Bertrand wrote a nice article Bad habits to kick : using old-style JOINs about it.
– Juan Carlos Oropeza
Oct 30 '17 at 21:33
Possible duplicate of Northwind SQL total orders per customer
– r2d2oid
Oct 30 '17 at 21:36
Promote the use of explict
JOIN sintaxis, Aaron Bertrand wrote a nice article Bad habits to kick : using old-style JOINs about it.– Juan Carlos Oropeza
Oct 30 '17 at 21:33
Promote the use of explict
JOIN sintaxis, Aaron Bertrand wrote a nice article Bad habits to kick : using old-style JOINs about it.– Juan Carlos Oropeza
Oct 30 '17 at 21:33
Possible duplicate of Northwind SQL total orders per customer
– r2d2oid
Oct 30 '17 at 21:36
Possible duplicate of Northwind SQL total orders per customer
– r2d2oid
Oct 30 '17 at 21:36
add a comment |
2 Answers
2
active
oldest
votes
0
SELECT s.CustomerID ,
p.FirstName +' '+p.LastName as Name,
count(SalesOrderID)
FROM Sales.Customer s
JOIN Person.Person p
ON s.CustomerID=p.BusinessEntityID
LEFT JOIN Sales.SalesOrderHeader so
ON s.Customer_ID = so.Customer_ID
GROUP BY s.CustomerID ,
p.FirstName +' '+p.LastName as Name
order by s.CustomerID
answered Oct 30 '17 at 21:35
Juan Carlos OropezaJuan Carlos Oropeza
36.8k63978
thank you for answering , the problem is there are many customers did not make any orders so they do not appear with this query
– George Kouki
Oct 30 '17 at 21:37
@GeorgeKouki Try aleft join
– Simon
Oct 30 '17 at 21:42
As @Simon said, use left join. Check edited version
– Juan Carlos Oropeza
Oct 30 '17 at 21:42
@Simon its works , thank you guys
– George Kouki
Oct 30 '17 at 21:44
add a comment |
0
I added CONCAT just as something new for u probably. Anyways the OVER(PARTITION BY column_name) clause was used as a replacement for GROUP BY since you are printing more columns than what a GROUP BY clause would allow.
edited Jan 2 at 4:58
Siong Thye Goh
1,48711016
answered Jan 2 at 1:35
Zabi SidiqkhilZabi Sidiqkhil
846
Welcome to Stackoverflow! Please do not add code (in this case the full query) as images. This makes it impossible to copy it or make edits in your post directly :)
– geisterfurz007
Jan 3 at 0:26
thanks a lot, i will note that down :)
– Zabi Sidiqkhil
Jan 3 at 2:08
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%2f47024467%2fadventure-works-2014-sql-queries%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
SELECT s.CustomerID ,
p.FirstName +' '+p.LastName as Name,
count(SalesOrderID)
FROM Sales.Customer s
JOIN Person.Person p
ON s.CustomerID=p.BusinessEntityID
LEFT JOIN Sales.SalesOrderHeader so
ON s.Customer_ID = so.Customer_ID
GROUP BY s.CustomerID ,
p.FirstName +' '+p.LastName as Name
order by s.CustomerID
answered Oct 30 '17 at 21:35
Juan Carlos OropezaJuan Carlos Oropeza
36.8k63978
thank you for answering , the problem is there are many customers did not make any orders so they do not appear with this query
– George Kouki
Oct 30 '17 at 21:37
@GeorgeKouki Try aleft join
– Simon
Oct 30 '17 at 21:42
As @Simon said, use left join. Check edited version
– Juan Carlos Oropeza
Oct 30 '17 at 21:42
@Simon its works , thank you guys
– George Kouki
Oct 30 '17 at 21:44
add a comment |
0
SELECT s.CustomerID ,
p.FirstName +' '+p.LastName as Name,
count(SalesOrderID)
FROM Sales.Customer s
JOIN Person.Person p
ON s.CustomerID=p.BusinessEntityID
LEFT JOIN Sales.SalesOrderHeader so
ON s.Customer_ID = so.Customer_ID
GROUP BY s.CustomerID ,
p.FirstName +' '+p.LastName as Name
order by s.CustomerID
answered Oct 30 '17 at 21:35
Juan Carlos OropezaJuan Carlos Oropeza
36.8k63978
thank you for answering , the problem is there are many customers did not make any orders so they do not appear with this query
– George Kouki
Oct 30 '17 at 21:37
@GeorgeKouki Try aleft join
– Simon
Oct 30 '17 at 21:42
As @Simon said, use left join. Check edited version
– Juan Carlos Oropeza
Oct 30 '17 at 21:42
@Simon its works , thank you guys
– George Kouki
Oct 30 '17 at 21:44
add a comment |
0
0
0
SELECT s.CustomerID ,
p.FirstName +' '+p.LastName as Name,
count(SalesOrderID)
FROM Sales.Customer s
JOIN Person.Person p
ON s.CustomerID=p.BusinessEntityID
LEFT JOIN Sales.SalesOrderHeader so
ON s.Customer_ID = so.Customer_ID
GROUP BY s.CustomerID ,
p.FirstName +' '+p.LastName as Name
order by s.CustomerID
answered Oct 30 '17 at 21:35
Juan Carlos OropezaJuan Carlos Oropeza
36.8k63978
SELECT s.CustomerID ,
p.FirstName +' '+p.LastName as Name,
count(SalesOrderID)
FROM Sales.Customer s
JOIN Person.Person p
ON s.CustomerID=p.BusinessEntityID
LEFT JOIN Sales.SalesOrderHeader so
ON s.Customer_ID = so.Customer_ID
GROUP BY s.CustomerID ,
p.FirstName +' '+p.LastName as Name
order by s.CustomerID
answered Oct 30 '17 at 21:35
Juan Carlos OropezaJuan Carlos Oropeza
36.8k63978
edited Oct 30 '17 at 21:42
answered Oct 30 '17 at 21:35
Juan Carlos OropezaJuan Carlos Oropeza
36.8k63978
answered Oct 30 '17 at 21:35
Juan Carlos OropezaJuan Carlos Oropeza
36.8k63978
answered Oct 30 '17 at 21:35
Juan Carlos OropezaJuan Carlos Oropeza
36.8k63978
36.8k63978
thank you for answering , the problem is there are many customers did not make any orders so they do not appear with this query
– George Kouki
Oct 30 '17 at 21:37
@GeorgeKouki Try aleft join
– Simon
Oct 30 '17 at 21:42
As @Simon said, use left join. Check edited version
– Juan Carlos Oropeza
Oct 30 '17 at 21:42
@Simon its works , thank you guys
– George Kouki
Oct 30 '17 at 21:44
add a comment |
thank you for answering , the problem is there are many customers did not make any orders so they do not appear with this query
– George Kouki
Oct 30 '17 at 21:37
@GeorgeKouki Try aleft join
– Simon
Oct 30 '17 at 21:42
As @Simon said, use left join. Check edited version
– Juan Carlos Oropeza
Oct 30 '17 at 21:42
@Simon its works , thank you guys
– George Kouki
Oct 30 '17 at 21:44
thank you for answering , the problem is there are many customers did not make any orders so they do not appear with this query
– George Kouki
Oct 30 '17 at 21:37
thank you for answering , the problem is there are many customers did not make any orders so they do not appear with this query
– George Kouki
Oct 30 '17 at 21:37
@GeorgeKouki Try a
left join– Simon
Oct 30 '17 at 21:42
@GeorgeKouki Try a
left join– Simon
Oct 30 '17 at 21:42
As @Simon said, use left join. Check edited version
– Juan Carlos Oropeza
Oct 30 '17 at 21:42
As @Simon said, use left join. Check edited version
– Juan Carlos Oropeza
Oct 30 '17 at 21:42
@Simon its works , thank you guys
– George Kouki
Oct 30 '17 at 21:44
@Simon its works , thank you guys
– George Kouki
Oct 30 '17 at 21:44
add a comment |
0
I added CONCAT just as something new for u probably. Anyways the OVER(PARTITION BY column_name) clause was used as a replacement for GROUP BY since you are printing more columns than what a GROUP BY clause would allow.
edited Jan 2 at 4:58
Siong Thye Goh
1,48711016
answered Jan 2 at 1:35
Zabi SidiqkhilZabi Sidiqkhil
846
Welcome to Stackoverflow! Please do not add code (in this case the full query) as images. This makes it impossible to copy it or make edits in your post directly :)
– geisterfurz007
Jan 3 at 0:26
thanks a lot, i will note that down :)
– Zabi Sidiqkhil
Jan 3 at 2:08
add a comment |
0
I added CONCAT just as something new for u probably. Anyways the OVER(PARTITION BY column_name) clause was used as a replacement for GROUP BY since you are printing more columns than what a GROUP BY clause would allow.
edited Jan 2 at 4:58
Siong Thye Goh
1,48711016
answered Jan 2 at 1:35
Zabi SidiqkhilZabi Sidiqkhil
846
Welcome to Stackoverflow! Please do not add code (in this case the full query) as images. This makes it impossible to copy it or make edits in your post directly :)
– geisterfurz007
Jan 3 at 0:26
thanks a lot, i will note that down :)
– Zabi Sidiqkhil
Jan 3 at 2:08
add a comment |
0
0
0
I added CONCAT just as something new for u probably. Anyways the OVER(PARTITION BY column_name) clause was used as a replacement for GROUP BY since you are printing more columns than what a GROUP BY clause would allow.
edited Jan 2 at 4:58
Siong Thye Goh
1,48711016
answered Jan 2 at 1:35
Zabi SidiqkhilZabi Sidiqkhil
846
I added CONCAT just as something new for u probably. Anyways the OVER(PARTITION BY column_name) clause was used as a replacement for GROUP BY since you are printing more columns than what a GROUP BY clause would allow.
edited Jan 2 at 4:58
Siong Thye Goh
1,48711016
answered Jan 2 at 1:35
Zabi SidiqkhilZabi Sidiqkhil
846
edited Jan 2 at 4:58
Siong Thye Goh
1,48711016
edited Jan 2 at 4:58
Siong Thye Goh
1,48711016
edited Jan 2 at 4:58
Siong Thye Goh
1,48711016
1,48711016
answered Jan 2 at 1:35
Zabi SidiqkhilZabi Sidiqkhil
846
answered Jan 2 at 1:35
Zabi SidiqkhilZabi Sidiqkhil
846
answered Jan 2 at 1:35
Zabi SidiqkhilZabi Sidiqkhil
846
846
Welcome to Stackoverflow! Please do not add code (in this case the full query) as images. This makes it impossible to copy it or make edits in your post directly :)
– geisterfurz007
Jan 3 at 0:26
thanks a lot, i will note that down :)
– Zabi Sidiqkhil
Jan 3 at 2:08
add a comment |
Welcome to Stackoverflow! Please do not add code (in this case the full query) as images. This makes it impossible to copy it or make edits in your post directly :)
– geisterfurz007
Jan 3 at 0:26
thanks a lot, i will note that down :)
– Zabi Sidiqkhil
Jan 3 at 2:08
Welcome to Stackoverflow! Please do not add code (in this case the full query) as images. This makes it impossible to copy it or make edits in your post directly :)
– geisterfurz007
Jan 3 at 0:26
Welcome to Stackoverflow! Please do not add code (in this case the full query) as images. This makes it impossible to copy it or make edits in your post directly :)
– geisterfurz007
Jan 3 at 0:26
thanks a lot, i will note that down :)
– Zabi Sidiqkhil
Jan 3 at 2:08
thanks a lot, i will note that down :)
– Zabi Sidiqkhil
Jan 3 at 2:08
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%2f47024467%2fadventure-works-2014-sql-queries%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
Promote the use of explict
JOINsintaxis, Aaron Bertrand wrote a nice article Bad habits to kick : using old-style JOINs about it.– Juan Carlos Oropeza
Oct 30 '17 at 21:33
Possible duplicate of Northwind SQL total orders per customer
– r2d2oid
Oct 30 '17 at 21:36