Selecting count with other columns through joined tables (Oracle SQL)

Question

I have the following query that returns the number of orders a stock item has had:

select count(Orders.OrderID) from Stocks, Orders
where Stocks.StockID = Orders.StockID(+) group by Stocks.StockID;

This returns:

count(Orders.OrderID)
---------------------
0
1
2
0
1
...

However, I also want to display the name of the particular stock item alongside this query. So far, I have tried this, but the following error occurs...

select Stocks.Name, count(Orders.OrderID) from Stocks, Orders
where Stocks.StockID = Orders.StockID(+) group by Stocks.StockID;

The following error occurs:

select Stocks.Name, count(Orders.OrderID) from Stocks, Orders
       *
Error: not a GROUP BY expression.

This should return:

Name        count(Orders.OrderID)
----------  ---------------------
Item1       0
Item2       1
Item3       2
Item4       0
Item5       1
.....       ...

Can anyone help? Thanks in advance.

Lukasz Szozda · Accepted Answer · 2019-11-16 20:17:08Z

4

It should be sth like:

select Stocks.StockID,Stocks.Name, count(Orders.OrderID) 
from Stocks
left join Orders
  on Stocks.StockID = Orders.StockID  -- explicit outer join syntax
group by Stocks.StockID,Stocks.Name;  -- matching with select column list

answered Nov 16, 2019 at 20:17

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JDS404 Over a year ago

Thanks very much! This seems to be the simplest solution.

Bob Jarvis - Слава Україні · Accepted Answer · 2019-11-16 22:19:04Z

0

I suggest that you use the COUNT analytic function:

SELECT DISTINCT s.NAME,
       COUNT(*) OVER (PARTITION BY s.STOCKID ORDER BY s.STOCKID) AS ON_ORDER
  FROM STOCKS s
  LEFT OUTER JOIN ORDERS o
    ON s.STOCKID = o.STOCKID

dbfiddle here

edited Nov 16, 2019 at 22:19

answered Nov 16, 2019 at 22:06

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Comments

Andi Pexton · Accepted Answer · 2019-11-17 02:40:21Z

0

Another option would be the following;

SELECT S.StockID, S.Name, Orders.[Count]
FROM Stocks AS S
    CROSS APPLY
    ( SELECT COUNT(*) AS [Count]
      FROM Orders AS O
      WHERE S.StockID = O.StockID
    ) AS Orders

:)

answered Nov 17, 2019 at 2:40

Andi Pexton

965 bronze badges

1 Comment

Andi Pexton Over a year ago

dbfiddle.uk/…

William Robertson · Accepted Answer · 2019-11-17 10:15:54Z

From your description, it seems that you want to report one aggregate row for each stock name, or in other words group by the stock name:

select s.name as stock_name, count(o.orderid) as orders
from   stocks s
       left join orders o
            on  o.stockid = s.stockid
group by s.name;

That will give results like

STOCK_NAME  ORDERS
----------- ------
X              123
Y               45
Z                6

You can include other columns from Stocks such as the Stock ID if you want:

select s.stockid, s.name as stock_name, count(o.orderid) as orders
from   stocks s
       left join orders o
            on  o.stockid = s.stockid
group by s.stockid, s.name;

which will give results like

STOCKID  STOCK_NAME  ORDERS
-------- ----------- ------
1        X              123
2        Y               45
3        Z                6

Collectives™ on Stack Overflow

Selecting count with other columns through joined tables (Oracle SQL)

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related