CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
Gowtham Pendyala
Gowtham Pendyala

Reputation: 175

Oracle 11g getting two counts and dividing them

I am trying to get two counts and divide them in Oracle 11g ... I tried two versions of it

1) 

SELECT 
  x.number/y.number 
FROM
(
  SELECT 
    COUNT(*) as "number" 
  FROM 
    GAME 
  WHERE 
    HOMETEAM='Blackpool' 
    AND FTR='H' 
    OR AWAYTEAM='Blackpool' 
    AND FTR='A'
) x 
join (
  SELECT COUNT(*) as "number" 
  FROM GAME 
  WHERE HOMETEAM='Blackpool' 
        OR AWAYTEAM='Blackpool'
)y;

I get the below error

ORA-01747: invalid user.table.column, table.column, or column specification 01747. 00000 - "invalid user.table.column, table.column, or column specification"

*Cause:
*Action: Error at Line: 1 Column: 10

2)

select 
  (
   SELECT COUNT(*) 
   FROM GAME 
   WHERE HOMETEAM='Blackpool' 
         AND FTR='H' 
         OR AWAYTEAM='Blackpool' 
         AND FTR='A'
   )     /
   (
   SELECT COUNT(*) 
   FROM GAME 
   WHERE HOMETEAM='Blackpool' 
   OR AWAYTEAM='Blackpool'
   );

After I run this one ..I get the below error

ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause:
*Action: Error at Line: 3 Column: 84

Upvotes: 1

Views: 428

Answers (2)

SRVFan
SRVFan

Reputation: 344

I have two examples for you of queries I have used in the past. The first gets you a percentage of ITEM count grouped by STATUS, and the second has the percentage, but is rounded:

SELECT
    STATUS,
    COUNT(ITEMNUM) AS NUMBER_OF_ITEMs,
    (COUNT(ITEMNUM) / (SELECT COUNT(ITEMNUM) FROM WOSTATUS WHERE DATE >= to_date('2015-01-01','YYYY-MM-DD'))) AS PERCENT_OF_ITEMs
FROM
    ITEMS
WHERE
    DATE >= to_date('2015-01-01','YYYY-MM-DD')
GROUP BY
    STATUS
ORDER BY
    STATUS ASC

I am rounding to 4 numbers in the float, but you can change this to whatever fits your needs.

SELECT
    STATUS,
    COUNT(ITEMNUM) AS NUMBER_OF_ITEMs,
    ROUND((COUNT(ITEMNUM) / (SELECT COUNT(ITEMNUM) FROM WOSTATUS WHERE DATE >= to_date('2015-01-01','YYYY-MM-DD'))), 4) AS PERCENT_OF_ITEMs
FROM
    ITEMS
WHERE
    DATE >= to_date('2015-01-01','YYYY-MM-DD')
GROUP BY
    STATUS
ORDER BY
    STATUS ASC

From these queries I get a table that shows ITEM counts by STATUS in the first 2 columns, and the percentage of total number of ITEMs in the final (count of 1 ITEM/count of all ITEMs).

Upvotes: 0

dcieslak
dcieslak

Reputation: 2715

SELECT x."number"/y."number" 
FROM
(SELECT COUNT(*) as "number" 
   FROM GAME 
 WHERE HOMETEAM='Blackpool' AND FTR='H' OR AWAYTEAM='Blackpool' AND FTR='A') x
cross join 
(SELECT COUNT(*) as "number" 
  FROM GAME 
  WHERE HOMETEAM='Blackpool' OR AWAYTEAM='Blackpool') y;

The Number is reserved word. If you insist to use it then always use " (double quotation). Use CROSS JOIN when you do not plan to have any ON clause. You can also use JOIN ON 1 = 1

select 
 (SELECT COUNT(*) 
  FROM GAME 
  WHERE HOMETEAM='Blackpool' AND FTR='H' OR AWAYTEAM='Blackpool' AND FTR='A')     
 /
 (SELECT COUNT(*) 
 FROM GAME 
 WHERE HOMETEAM='Blackpool' OR AWAYTEAM='Blackpool') 
 from dual

Add From dual and it will work fine.

Upvotes: 3

Related Questions