Taking Count using inline query statement

Question

I am trying to get a count of columns from 2 tables. I have written a SQL statement like this :

SELECT COUNT (assetnum) asset, COUNT (description) description, COUNT ( CASE WHEN totdowntime > 0 THEN assetnum END) totdowntime, (SELECT COUNT (wonum) FROM workorder WHERE assetnum IN (SELECT assetnum FROM asset WHERE TO_CHAR (installdate,'YYYY') ='2011' ) ) AS wo_count FROM asset WHERE TO_CHAR (installdate, 'YYYY') = '2011

I am not getting it why the above code is not working even the inline statement is returning single value ?

The data is like this:

asset table :

assetnum   description  totdowntime  insatlldate   siteid
1000        ABC         1            01-01-2011    AGN
1001        DEF         1.5          02-01-2011    AGN
1002        AKK         5            03-01-2011    AGN
1003        LSDL        3            04-01-2011    AGN
1004        JKDF        3.3          01-02-2012    AGN
1005        DFJK        4.5          01-02-2012    AGN

Workorder table :

wonum   siteid  assetnum
  1      AGN    1000
  2      AGN    1001
  3      AGN    1002
  4      DVN    1000
  5      DVN    1001
  6      AGN    1004

Desired Output :

asset_cnt   descrpition_cnt totdowntime_cnt wonum_cnt
    6          6             6                4

The Output I get :

Error:ORA-00937: not a single-group group function

Answer 1

You can use below query,

SELECT COUNT (A.assetnum) AS asset, COUNT (A.description) AS description,
COUNT ( A.totdowntime) AS totdowntime,
COUNT (AN.wonum)
FROM asset A
INNER JOIN workorder AN
ON (A.assetnum  = AN.assetnum )

WHERE TO_CHAR (A.installdate, 'YYYY') = '2011';

Use alias for installdate as per the table A or AN You can use case where it is required

COUNT (CASE WHEN A.totdowntime > 0 THEN A.assetnum END)

Answer 2

I suspect that you want this:

SELECT COUNT(a.assetnum) as asset,
       COUNT(a.description) as description,
       SUM(CASE WHEN a.totdowntime > 0 AND a.assetnum IS NOT NULL THEN 1 ELSE 0 
           END) as totdowntime,
       (SELECT COUNT(wo.wonum)
        FROM workorder wo
        WHERE wo.assetnum = a.assetnum
       ) as wo_count
FROM asset a
WHERE installdate >= DATE '2011-01-01' AND
      installdate < DATE '2012-01-01';

Notes:

• The first two COUNT() are probably going to return the same value. Remember that COUNT() counts the number of non-NULL values.
• I prefer SUM() rather than COUNT() for counting values from expressions. But COUNT() is also fine.
• I think you want a correlated subquery for counting from workorder.
• Table aliases and qualified column names are a good idea in any query.