Nested sql count from two count queries into one query

Question

I am struggling with a way to combine two queries into one (obvious i am not an sql guru ;))

SELECT COUNT(orderid) AS TotalAmountOfOrders
FROM tableorder
WHERE (YEAR(orderDate) BETWEEN 2012 AND 2012)


SELECT COUNT(errorid) AS AmountOfErrorsOnOrders
FROM  tableError                      
WHERE (YEAR(errorDate) BETWEEN 2012 AND 2012)

The problem is if i am just adding them as

SELECT COUNT(orderid) AS ...,COUNT(errorid) AS ...
From tableorder inner join tableError

I am not getting the total amount but only the amount of orders with errors cause of the way of how i am bringing them together.

So how could i get both counts in one query?

How are the two tables linked - is there an order id field in the table_error table? — Dibstar, Apr 17 '12 at 07:29

score 3 · Accepted Answer · answered Apr 17 '12 at 07:30

3

You could do it as a UNION:

SELECT COUNT(orderid) AS counted
FROM tableorder
WHERE (YEAR(orderDate) BETWEEN 2012 AND 2012)

UNION

SELECT COUNT(errorid) AS counted
FROM  tableError                      
WHERE (YEAR(errorDate) BETWEEN @Year1 AND @Year2)
GROUP BY SurName, FirstName;

Then the first row will be the count from tableorder, and the second row will be the count from tableError.

answered Apr 17 '12 at 07:30

Phil Cairns

758
4
15

this close to what i want and i can use it. Another solution which i got some advise in is to just use a nested select like: SELECT COUNT(orderid) AS TotalOrders, (SELECT COUNT(errorid) FROM tableError WHERE tableOrder.orderid=tableError.orderid and date between x and y) AS AmountOfOrdersWithErrors FROM tableOrders...Perhaps not the best practice way but its enough for what its going to be used for... – User Apr 17 '12 at 08:32

score 0 · Answer 2 · edited May 23 '17 at 10:34

This will return the values as two columns.

SELECT 
  (
    SELECT COUNT(orderid)
    FROM tableorder
    WHERE (YEAR(orderDate) BETWEEN 2012 AND 2012)
  ) AS TotalAmountOfOrders,
  (
    SELECT COUNT(errorid)
    FROM  tableError                      
    WHERE (YEAR(errorDate) BETWEEN 2012 AND 2012)
  ) AS AmountOfErrorsOnOrders

I recommend that you check your dates against an interval instead of applying a function on a column.

SELECT 
  (
    SELECT COUNT(orderid)
    FROM tableorder
    WHERE orderDate >=  '20120101' AND OrderDate < '20130101'
  ) AS TotalAmountOfOrders,
  (
    SELECT COUNT(errorid)
    FROM  tableError                      
    WHERE errorDate >=  '20120101' AND errorDate < '20130101'
  ) AS AmountOfErrorsOnOrders

What makes a SQL statement sargable?

thnx, although i want 1 column but it works. Yeah, im using it as a function and calling it via a buttonclick winform. Its an old project which i got to add to my list of things to maintain :D — User, Apr 17 '12 at 08:30

score 0 · Answer 3 · answered Apr 17 '12 at 07:31

Try this:

SELECT COUNT(o.orderid), COUNT(e.errorid) AS TotalAmountOfOrders 
FROM tableorder o
INNER JOIN tableError e ON o.orderid = e.orderid
WHERE (YEAR(o.orderDate) BETWEEN 2012 AND 2012)
AND   (YEAR(e.errorDate) BETWEEN 2012 AND 2012)
GROUP BY o.orderid

Nested sql count from two count queries into one query

3 Answers3