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

CodePudding user response：

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

CodePudding user response：

You can do this by transforming your subquery to a CROSS APPLYof a pre-aggregated table

SELECT
  c.id,
  c.companyname,
  c.orgtype,
  ins.total,
  ins.orders
FROM customers c
CROSS APPLY (
    SELECT
      COUNT(*) AS orders,
      ISNULL(SUM(ins.invoicetotal), 0) AS total
    FROM invoice_summary ins
    WHERE ins.cid = c.ID 
      AND ins.submitted between '20220801' and '20220831'
    GROUP BY ()  -- do not remove the GROUP BY
) ins
WHERE c.orgtype = 10
ORDER BY
  ins.total DESC;

You can also do this with an INNER JOIN against it

SELECT
  c.id,
  c.companyname,
  c.orgtype,
  ins.total,
  ins.orders
FROM customers c
INNER JOIN (
    SELECT
      ins.cid,
      COUNT(*) AS orders,
      ISNULL(SUM(ins.invoicetotal), 0) AS total
    FROM invoice_summary ins
    WHERE ins.submitted between '20220801' and '20220831'
    GROUP BY ins.cid
) ins ON ins.cid = c.ID 
WHERE c.orgtype = 10
ORDER BY
  ins.total DESC;

CodePudding user response：

Quick and dirty way would be to dump your results into a temp table, delete the records you don't want, then select what remains.

Add this to the end of your select before the FROM clause:

INTO #temptable

Then delete the records you don't want:

DELETE FROM #temptable WHERE [Orders] = 0

Then just select from the temp table.

There are other ways to do this, and you should read up on the downsides of temp tables before implementing this solution.