SQL Selecting & Counting From Another Table

I have this query that works excellently and gives me the results I want, however, does anybody know how I can remove any rows that have 0 orders? I am sure it is something simple, I just can’t get my head around it.

In other words, should it only show the top 2 rows?

SELECT customers.id, customers.companyname, customers.orgtype,
   (SELECT COALESCE(SUM(invoicetotal), 0)
    FROM invoice_summary
    WHERE invoice_summary.cid = customers.ID 
    and invoice_summary.submitted between '2022-08-01' and '2022-08-31'
    ) AS total,
    (SELECT COUNT(invoicenumber)
    FROM invoice_summary
    WHERE invoice_summary.cid = customers.ID 
    and invoice_summary.submitted between '2022-08-01' and '2022-08-31'
    ) AS orders
FROM customers WHERE customers.orgtype = 10
 ORDER BY total DESC

>Solution :

You can use a CTE to keep the request simple :

WITH CTE_Orders AS (        
SELECT customers.id, customers.companyname, customers.orgtype,
       (SELECT COALESCE(SUM(invoicetotal), 0)
        FROM invoice_summary
        WHERE invoice_summary.cid = customers.ID 
        and invoice_summary.submitted between '2022-08-01' and '2022-08-31'
        ) AS total,
        (SELECT COUNT(invoicenumber)
        FROM invoice_summary
        WHERE invoice_summary.cid = customers.ID 
        and invoice_summary.submitted between '2022-08-01' and '2022-08-31'
        ) AS orders
    FROM customers WHERE customers.orgtype = 10
     ORDER BY total DESC
)
SELECT * FROM CTE_Orders WHERE orders > 0

You will find aditionals informations about CTE on Microsoft documentation : https://learn.microsoft.com/fr-fr/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16