27173

AdventureWorks2012: For each customer, determine the number of orders created in year 2007. Show 0 f

Question:

AdventureWorks2012: Sql For each customer, determine the number of orders created in year 2007. If a customer has not created any order in year 2004, show 0 for that customer. Show: customer ID, # of orders created in 2007.

Answer1:

Same approach as in my other response - use a CTE (Common Table Expression) to determine number of sales for each customer in the year 2007:

-- determine the number and total of all sales in 2007 ;WITH SalesPerCustomer AS ( SELECT c.CustomerID, NumberOfSales = ISNULL(COUNT(soh.SalesOrderID), 0) FROM Sales.Customer c INNER JOIN Sales.SalesOrderHeader soh ON soh.CustomerID = c.CustomerID AND soh.OrderDate >= '20070101' AND soh.OrderDate < '20080101' GROUP BY c.CustomerID ) SELECT CustomerID , NumberOfSales FROM SalesPerCustomer ORDER BY NumberOfSales DESC

Ordered the output by number of sales descending, so you'll get the customer with the most sales first

Recommend

  • How to Calculate Cumulative time difference in sql
  • Lead() and LAG() functionality in SQL Server 2008
  • getting the range of a number column(min/max) if there are missing numbers in between
  • Sending mail with ASP.NET
  • enumerate days of week in t-sql
  • Python Beginner: How can I make text #1 look like text #2
  • LINQ join with filter criteria
  • SQL getting multiple columns into one column
  • Divide a $1 by 3 and adjusting 1 cent
  • SQL: Getting the physical size of a subset of a table
  • Less Conflicting Session Manager for Zope 2
  • pip in virtualenv gets ConnectTimeoutError
  • NUnit 3.0 TestCase const custom object arguments
  • Plotting line graph with factors in R
  • Does Mobilefirst provide a provision to access web services directly?
  • Marklogic : Query response time is very high
  • Create DicomImage from scratch using Dcmtk
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • How to do unit test for HttpContext.Current.Server.MapPath
  • FFmpeg Conversion Error
  • Installing Hadoop, Java Exception about illegal characters at index 7?
  • Different response to non-authenticated users and AJAX calls
  • javascript inside java/jsp code
  • Sending data from AppleScript to FileMaker records
  • Timeout for blocking function call, i.e., how to stop waiting for user input after X seconds?
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • Is there a mandatory requirement to switch app.yaml?
  • Comma separated Values
  • File upload with ng-file-upload throwing error
  • How to delete a row from a dynamic generate table using jquery?
  • ExecuteAsync RestSharp to allow backgroundWorker CancellationPending c#
  • json Serialization in asp
  • KeystoneJS: Relationships in Admin UI not updating
  • AngularJs get employee from factory
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Load html files in TinyMce
  • Trying to get generic when generic is not available
  • How to stop GridView from loading again when I press back button?
  • How does Linux kernel interrupt the application?