Oracle SQL - GROUP BY not working

Question

When I am run the below query, I get the following result.

I don't understand why I am not getting the expected result from the GROUP BY.

How can I fix this so I get the expected result?

SELECT
  status AS "ROW LABELS",
  (case when order = 'INTERNET' THEN COUNT(*) end) AS "INTERNET"
FROM
    order
    ,status
WHERE  order       =   status
GROUP BY status, order_source
order by 1;

Data is here https://drive.google.com/file/d/0BzWMxMDCgXFaSk5TTWV2SEEzTFU/edit?usp=sharing I couldn't format the data here well.

Following Dnoeth's answer, I am getting the below result: enter image description here See, I need the row labels to be grouped up, but they're not being grouped. Putting count outside the case helped a little. I have edited the query to get the idea across, the query is more complicated than what I am presenting here

What is the expected result? Your query works so we need to know what you are after to be able to help. — SQLChao
– SQLChao, Commented Jul 16, 2014 at 20:49
That can't be your real query, because WHERE order = status will generate an error as order is a reserved word. — user330315
– user330315, Commented Jul 16, 2014 at 21:10

dnoeth · Accepted Answer · 2014-07-16 20:35:30Z

1

Assuming you have some typo (order instead of order_source) you need to move the CASE inside the COUNT:

SELECT
  status AS "ROW LABELS",
  COUNT(case when order_source = 'INTERNET' THEN 1 end) AS "INTERNET"
FROM
    order
    ,status
WHERE  order       =   status
GROUP BY status
order by 1;

answered Jul 16, 2014 at 20:35

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

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

1 Comment

Noman Arain Over a year ago

Hey thx, I've updated. I didn't notice the format until after posting. Yea just typo here, I'm not getting any errors. I've uploaded an image showing what I mean.

Collectives™ on Stack Overflow

Oracle SQL - GROUP BY not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related