0

I need to create a VIEW that shows a TOP 3 best selling products in June 2022, the total number of products sold in the month, the total profits and all customers who bought the specific product in June 2022. For now I tried with this:

CREATE VIEW INVOICE_VIEW AS
SELECT TOP 3 P.ProductName, 
    SUM(F.Quantity) AS TotalQty, 
    SUM(F.Price) AS TotalProfit, 
    C.Name + ' ' + C.LastName AS Customers
FROM PRODUCT P
INNER JOIN INVOICE F
    ON P.idProduct = F.Det_Products
LEFT JOIN CUSTOMER C
    ON F.Num_Invoice = C.Num_Invoice
WHERE F.Date BETWEEN '20220601' AND '20220630'
GROUP BY ProductName, Name, LastName
ORDER BY TotalProfit ASC

Using SELECT * FROM INVOICE_VIEW creates this:

ProductName TotalQty TotalProfit Customers
Cheetos 10 3 Andrea G.
Ruffles 15 7.5 Kevin T.
DeTodito 55 33 NULL

As you can see, it shows most of what I require, but in the last row it shows NULL instead of the Customer's Name. And that happens whenever a product has more than one customer. I would like to display something like this:

ProductName TotalQty TotalProfit Customers
Cheetos 10 3 Andrea G.
Ruffles 15 7.5 Kevin T.
DeTodito 55 33 Carlos Z., Robin C.

It should show the name of all customers/clients, which could be more than 1, in the same cell. Or at least something like this:

ProductName TotalQty TotalProfit Customer1 Customer2 Customer3
Cheetos 10 3 Andrea G. NULL or EMPTY NULL or EMPTY
Ruffles 15 7.5 Kevin T. NULL or EMPTY NULL or EMPTY
DeTodito 55 33 Carlos Z. Robin C. NULL or EMPTY
Dale K
  • 25,246
  • 15
  • 42
  • 71
Renny GaSal
  • 1
  • 1
  • 1
    Does this answer your question? [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) – Thom A Jul 27 '22 at 08:26
  • A null value for customer in your "current results" suggests you have schema problems - perhaps you should address that first? As written, your query does not match your goal. You need to calculate totals for each product and THEN find the associated customers. One big purchase of Cheetos by Bob will hide the fact that everyone else loves Ruffles but buy smaller quantities (though the total is still higher). And summing price is generally not "profit" - usually you sum price * quantity. – SMor Jul 27 '22 at 10:26
  • [string_agg](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16) could be usefull – GuidoG Jul 27 '22 at 11:16

0 Answers0