Kindly help me with the right syntax of mine, really appreciate it, if you could just edit my existing syntax into correct one without alternate options. thanks in advance
CREATE TABLE customer ( id INTEGER PRIMARY KEY, customer_name TEXT, dept TEXT ); CREATE TABLE invoice ( invoice_number INTEGER PRIMARY KEY, customer_id INTEGER, time_Canceled DATETIME, time_refunded DATETIME, time_issued DATETIME, time_paid DATETIME, total_price float );
Now the question requires to find customer_name, number_of_invoices, lifetime_Value, most_recent_invoice_time, most_recent_invoice_total_price order by lifetime value desc and customer_name
number_of_invoices is Total number of invoices assosiated with the custmer over al time, regardless whether invoice was cancelled or refunded. return zero if that customer has no invoices.
lifetime_Value - This is the Sum of how much a customer has spent (total_price) totaled across all invoices. exclude invoices which were cancelled or refunded. return zero if that customer has no qualifying invoices. all values should be returned zero as an unassigned integer.
most_recent_invoice_time - the most recent time an invoice was issued for a customer, regardless whether it is cancelled or refunded. Return NULL if that customer has no invoices.
most_recent_invoice_total_price - this is when Sum of how much a customer has spent ('total_price') with most recent time an invoice was paid for a customer totaled across al invoices regardless whether it is cancelled or refunded. return NULL if that customer has no qualifying invoices. all values should be returned zero as an unassigned integer.
Below is the error - Invalid use of group function
Below is my query i have worked
SELECT cu.customer_name, (CASE WHEN inv.invoice_number IS NOT NULL THEN count(inv.invoice_number) ELSE 0 END) as number_of_invoices , SUM( CASE WHEN (inv.time_Canceled AND inv.time_refunded IS NOT NULL) Then inv.total_price Else 0 End ) as lifetime_Value, (CASE WHEN inv.invoice_number IS NOT NULL Then max(inv.time_issued) -- ORDER BY inv.time_issued DESC limit 1 Else NULL End) as most_recent_invoice_time, SUM( CASE WHEN inv.invoice_number IS NOT NULL Then max(inv.total_price) -- ORDER BY inv.time_paid DESC limit 1 Else NULL End ) as most_recent_invoice_total_price FROM customer cu inner join invoice inv ON cu.id = inv.customer_id GROUP BY cu.customer_name, most_recent_invoice_time ORDER BY lifetime_Value DESC , customer_name