January 30, 2014 at 10:54 am
I am trying to create a query that will give me customer and order information the scenario is this:
TableA has customer information such as:
CustomerNumber
Firstname
Lastname
Address
City
State
Zip
TableB has order information such as:
CustomerNumberKey
OrderNumber
I would like to query this:
Select
a.customernumber
,a.firstname
,a.lastname
,b.ordernumber
,count of ordernumbers from b for each customer<-- this is the part I can’t figure out.
From
Tablea a
Join tableb b
On a.customernumber = b.customernumberkey
Can someone help me figure out the part I don’t get? Thanks
January 30, 2014 at 11:12 am
robert.wiglesworth (1/30/2014)
I am trying to create a query that will give me customer and order information the scenario is this:TableA has customer information such as:
CustomerNumber
Firstname
Lastname
Address
City
State
Zip
TableB has order information such as:
CustomerNumberKey
OrderNumber
I would like to query this:
Select
a.customernumber
,a.firstname
,a.lastname
,b.ordernumber
,count of ordernumbers from b for each customer<-- this is the part I can’t figure out.
From
Tablea a
Join tableb b
On a.customernumber = b.customernumberkey
Can someone help me figure out the part I don’t get? Thanks
SELECT
a.CustomerNumber
, a.FirstName
, a.LastName
, COUNT(b.OrderID) AS OrderCount
FROM a INNER JOIN b ON a.CustomerNumber = b.CustomerNumberKey
GROUP BY a.CustomerNumber, a.FirstName, a.LastName
January 30, 2014 at 11:15 am
Do you want one record per customer or one per order?
January 30, 2014 at 11:32 am
One record per customer.
January 30, 2014 at 11:33 am
Thanks!
this works, but I was hoping not to have to do a GROUP BY because there are actually several more fields being selected than shown. Just more typing, but it will work.
Thanks again.
January 30, 2014 at 12:30 pm
If you're using 2012, then you can use PARTITION and COUNT, then you don't need GROUP BY.
January 30, 2014 at 1:16 pm
pietlinden (1/30/2014)
If you're using 2012, then you can use PARTITION and COUNT, then you don't need GROUP BY.
And if you're not, you can just group by customer_id in a CTE with the sum, then join it back to the customer table to get all the other non aggregated fields.
January 30, 2014 at 1:19 pm
I am using 2012. Can you give me an example of using the PARTITION and COUNT function?
January 30, 2014 at 1:28 pm
Never mind. I found an example on Technet and it worked perfectly. Thanks a bunch!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply