Search code examples
sql

count the number of preliminary and final invoices for each customer

I need count the number of preliminary and final invoices for each customer.

I used this query, but the sum is always = 0

SELECT 
    cust,
    SUM(CASE WHEN typ = 'فاتورة عرض' THEN 1 ELSE 0 END) AS total_preliminary_invoices,
    SUM(CASE WHEN typ = 'فاتورة نهائية' THEN 1 ELSE 0 END) AS total_final_invoices,
    COUNT(*) AS total_invoices 
FROM 
    dbo.invmang 
GROUP BY 
    cust;

the code

the table

This is the picture of code and the table, I have customers having more than invoice and type for each invoice (preliminary or final).

I am making report for every customer and how many preliminary invoice have and how many final invoice have

Solution

  • Your first count counts the number of "view invoices" which differs from preliminary invoice, so you had a typo in your search. In your sample data there is no match for your second text to count in typ. However, this is not a typo problem, but a data representation problem. You should create a table for typs which will have a field for id and another for text as well as a key. So your records would look like this:

    insert into typs(id, label, code)
values
(1, '<arabic text1>', 'PRELIMINARY_INVOICE'),
(2, '<arabic text2>', 'FINAL_INVOICE');

    and then you could do something like this:

    SELECT 
cust,
SUM(CASE WHEN typs.code = 'PRELIMINARY_INVOICE' THEN 1 ELSE 0 END) AS total_preliminary_invoices,
SUM(CASE WHEN typs.code = 'FINAL_INVOICE' THEN 1 ELSE 0 END) AS total_final_invoices,
COUNT(*) AS total_invoices FROM 
dbo.invmang
join dbo.typs
on dbo.invmang.typ = dbo.typs.id
GROUP BY 
cust;

    So in the future whenever you change these labels, you will only change the label in typs and whenever you want to show the text, join typs and get the label. Of course, unfortunately you will need to adjust the data you already have to be changed to numeric and to have the correct data for each and every record.