Invoice Line Count (chinook database)

Medium
26
94.0% Acceptance

Your task is to write SQL queries to solve a series of challenges based on the chinook database. While constructing your queries, make sure to keep the following points in mind:

  1. Stick to the table and column names exactly as defined in the chinook database schema. Deviations from the original names may result in incorrect results.

  2. Use double quotes around table and column names for clarity, like "TableName".

  3. When creating your solution, ensure the ORDER BY clause is used wherever applicable to avoid potential output mismatches.

  4. Ensure your output column names match exactly with what's mentioned in the challenge description.

Database Schema

Here's a quick rundown of the chinook database schema:

  • Tables:
    • Artist: Contains columns "ArtistId" and "Name".
    • Album: Contains columns "AlbumId", "Title", and "ArtistId".
    • Track: Holds information related to individual tracks.
    • MediaType, Genre, Playlist, PlaylistTrack, Invoice, InvoiceLine, Customer, and Employee: Various other tables with their respective columns.

Challenges

Objective: Your task is to determine the number of line items associated with each invoice.

Description:
In any business, an invoice can contain multiple line items detailing the products or services being charged. For this challenge, you need to find out how many line items are there in every invoice.

Tables to Use:

  • Invoice
  • InvoiceLine

Instructions:

  1. Join the Invoice and InvoiceLine tables using the appropriate key.
  2. Group the results by Invoice.InvoiceId.
  3. Count the number of line items for each invoice. The result should have a column named LineCount that represents this count.
  4. Display the Invoice.InvoiceId and LineCount.
  5. Order your results by Invoice.InvoiceId.

Expected Output Columns:

  • Invoice.InvoiceId
  • LineCount