SQLTeam.com | Weblogs | Forums

Help with Query

Hello, I am starting to learn SQL and I am stuck on a question: They are asking me: Show the number of orders placed by each customer (a customer has many invoiceId) and sort the result by the number of orders in descending order. What is the number of items placed for the 8th person on this list?
. I really have no idea how to do it and I would really appreciate your help. Here is the ER Diagram:

R u interested in ONLY the final SQL

or learning
how to get to the final SQL !!!

I can do both

:slight_smile

I would like both please!

please see if you understand this .. !!
Hope this helps !! :slight_smile: :slight_smile:

Or i can make it much simpler to understand

--------------------------------------------------------------
Line 1 = Show the number of orders placed by each customer 
--------------------------------------------------------------
Look AT the ER Diagram .. TO see WHERE you can GET this information FROM !!! 

Looking AT the TOP part Artists Albums Tracks .. It Looks LIKE ... related TO music 

Looking AT the bottom tables .. Tables = Employees , Customers , Invoices 
Looks LIKE related TO orders placed BY customer 

Which COLUMN is .. Orders AND which COLUMN IS customer

TABLE = customers COLUMN = Customer ID  .. will give customer 

Customer ID IS PRIMARY KEY which means each value IS UNIQUE ... which means each customer 

Which COLUMN IS ORDER

Invoices TABLE .. Invoice means ORDER 

Invoices TABLE .. COLUMN Invoice ID IS PRIMARY KEY which means each value IS UNIQUE ... which means each ORDER 

FOR each customer orders means
I have TO JOIN customers TABLE TO Invoices TABLE 
Why because customer info IS there IN customers TABLE 
AND ORDER info IS there IN Invoices TABLE 

JOIN ON what COLUMN ..
Typically joins ARE between PK - FK PRIMARY KEY .. FOREIGN KEY 

customerID IN Customer TABLE IS PK 
customer ID IN Invoices TABLE IS FK 

SELECT
    *
FROM
    [customers] AS [a]
    JOIN [Invoices] AS [b]
        ON [a].[customerid] = [b].[customerid]


FOR Each customer .. Orders means .. TSQL Lanaguage GROUP BY

SELECT
    [a].[customer_id]
    , COUNT([b].[InvoiceID])
FROM
    [customers] AS [a]
    JOIN [Invoices] AS [b]
        ON [a].[customerid] = [b].[customerid]
GROUP BY
    [a].[customer_id]


The parts WHERE you might find difficulty IS IN 
Typically joins ARE between PK - FK PRIMARY KEY .. FOREIGN KEY  .. what does this mean 
FOR Each customer .. Orders means .. TSQL Lanaguage GROUP BY


Line 1 = Show the number of orders placed by each customer 
this is the answer .. !! 
   SELECT
        [a].[customer_id]
        , COUNT([b].[InvoiceID])
    FROM
        [customers] AS [a]
        JOIN [Invoices] AS [b]
            ON [a].[customerid] = [b].[customerid]
    GROUP BY
        [a].[customer_id]
1 Like

Thank you very much, and yes, I did understand. I'm very thankfull for your help! :blush:

If you're only showing the CustomerId, and not the Customer Name, then you don't need the Customers table at all. Also, to sort the result, you must include an ORDER BY clause.

SELECT CustomerId, COUNT(*) AS Invoice_Count
FROM dbo.Invoices
GROUP BY CustomerId
ORDER BY COUNT(*) DESC /* DESC specifies a descending sort, that is, from the highest number to the lowest number */