How to use a SQL sub query?

Question

I am attempting to use a sub query to query our order database and return 3 columns for example:

Date          Orders          Replacements
09-MAY-14     100             5
...           ...             ...

Each order that is created can be given a reason, which basically means that it is a replacement product i.e. orders without a reason are new orders and orders with a reason are replacement orders.

I am using the below query in an attempt to get this information, but I'm getting lots of error messages, and each time I think I've fixed one I create another 10, so assume I completely have the wrong idea here.

SELECT Orders.EntryDate AS "Date", COUNT(Orders.OrderNo) AS "Orders",
  (SELECT COUNT(Orders.OrderNo) AS "Replacements"
  FROM Orders
  WHERE Orders.Reason IS NOT NULL
  AND Orders.EntryDate = '09-MAY-2014'
  AND Orders.CustomerNo = 'A001'
  GROUP BY Orders.EntryDate
  )
FROM Orders
WHERE Orders.Reason IS NULL
AND Orders.EntryDate = '09-MAY-2014'
AND Orders.CustomerNo = 'A001'
GROUP BY Orders.EntryDate
;

xQbert · Accepted Answer · 2014-05-09 12:46:00Z

1

Why the sub query use a case!

SELECT Orders.EntryDate AS "Date", COUNT(Orders.OrderNo) AS "Orders",
sum(CASE WHEN Orders.reason is null then 1 else 0 end) as "Replacements"
FROM Orders
WHERE Orders.Reason IS NULL
AND Orders.EntryDate = '09-MAY-2014'
AND Orders.CustomerNo = 'A001'
GROUP BY Orders.EntryDate

The subquery has to execute each time, since you need to evaluate each record the case can do that for you and then sum the results. If you need to get a count of -non replacement orders then just do a different case instead of a count.

answered May 9, 2014 at 12:46

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

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

Comments

Mureinik · Accepted Answer · 2014-05-09 13:00:17Z

0

You could could sum a case expression instead of having a another subquery with another where clause:

SELECT   Orders.EntryDate AS "Date", 
         SUM (CASE WHEN Orders.Reason IS NULL THEN 1 ELSE 0 END) AS "Orders",
         SUM (CASE WHEN Orders.Reason IS NOT NULL THEN 1 ELSE 0 END) AS "Replacements"
FROM     Orders
WHERE    Orders.EntryDate = '09-MAY-2014'
AND      Orders.CustomerNo = 'A001'
GROUP BY Orders.EntryDate

edited May 9, 2014 at 13:00

answered May 9, 2014 at 12:47

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Comments

Dan Bracuk · Accepted Answer · 2014-05-09 12:45:52Z

0

Your errors were probably due to the fact that you did not include the subquery in your group by clause. You can try that approach but this one is simpler:

select entrydate "date"
, count(orderno) "orders"
, sum(case when reason is not null then 1 else 0 end) "replacements"
etc
group by entrydate

answered May 9, 2014 at 12:45

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Comments

Lennart - Slava Ukraini · Accepted Answer · 2014-05-09 12:48:29Z

0

Is this what you are trying to do?

SELECT Orders.EntryDate , COUNT(case when Orders.reason is null then 1 end) AS orders , COUNT(case when Orders.reason is not null then 1 end) AS Replacements FROM Orders WHERE Orders.EntryDate = '09-MAY-2014' AND Orders.CustomerNo = 'A001' GROUP BY Orders.EntryDate

The Replacements expression can be simplified to:

COUNT(Orders.reason)

answered May 9, 2014 at 12:48

Lennart - Slava Ukraini

7,2111 gold badge23 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to use a SQL sub query?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related