My sql query to oracle query conversion

Question

I have below database structure

CREATE TABLE CUSTOMERS (
 custid char(4) constraint cust_pk primary key,
 firstname varchar(20),
 lastname varchar(25),
 city varchar(20),
 country varchar(20),
 creditlimit number(8,2)
);

CREATE TABLE PRODUCTS (
 prodID char(4) constraint prod_pk primary key,
 pname varchar(20),
 description varchar(50),
 category varchar(7), -- product category
 listprice numeric(5,2), -- list price
 weight numeric(4,1) -- weight 
);

CREATE TABLE SALES (
 saleno char(5) constraint sales_pk PRIMARY KEY,
 sdate date,
 paymentmethod varchar(20),
 custid char(4),
 constraint sales_fk_cust FOREIGN KEY (custid) references CUSTOMERS
);


CREATE TABLE SALESLINES (
 saleno char(5),
 prodid char(4),
 qty numeric(5),
 unitprice numeric(5,2),
 constraint sales_lines_pk PRIMARY KEY (saleno, prodid),
 constraint saleslines_fk_sale FOREIGN KEY (saleno) references SALES,
 constraint saleslines_fk_prod FOREIGN KEY (prodid) references PRODUCTS
);



INSERT INTO CUSTOMERS VALUES('C002', 'Ruby', 'Ringer','Springfield', 'Canada',125000);
INSERT INTO CUSTOMERS VALUES('C003', 'Bob', 'Bennett','Tucson','USA',50000);
INSERT INTO CUSTOMERS VALUES('C004', 'Pat', 'Rowling','Ottowa','Canada', 129000);
INSERT INTO CUSTOMERS VALUES('C905', 'Sue', 'Smith','Riverside','USA', 125000);
INSERT INTO CUSTOMERS VALUES('C005', 'Jim', 'Jason','New York', 'USA',25000);
INSERT INTO CUSTOMERS VALUES('C101', 'Darcy', 'Doe','Tucson','USA', 14500);
INSERT INTO CUSTOMERS VALUES('C104', 'Dan', 'Doe','Hermosillo','Mexico',10100);
INSERT INTO CUSTOMERS VALUES('C505', 'Sue', 'Smith','Tucson','USA', 19500);
INSERT INTO CUSTOMERS VALUES('C125', 'Bill', 'Jackson','Vancouver','Canada', 75000);




INSERT INTO PRODUCTS VALUES ('P051', '19" Monitor', 'Widescreen, black', 'Display', 114.95, 17.5);
INSERT INTO PRODUCTS VALUES ('P055', '27" Monitor', 'Widescreen, LCD ultra-sharp', 'Display', null, 50);
INSERT INTO PRODUCTS VALUES ('P012', 'Keyboard', 'Black, full size keys', 'Input', 14.75, 2);
INSERT INTO PRODUCTS VALUES ('P011', 'Keyboard', 'Ergonomic, soft touch keys', 'Input', 45.25, 2.5);
INSERT INTO PRODUCTS VALUES ('P074', 'Optical Mouse', '2-button mouse, basic', 'Input', 9.99, 1);
INSERT INTO PRODUCTS VALUES ('P075', 'Optical Mouse', 'Compact notebook optical mouse', 'Input', 24.99, 0.5);
INSERT INTO PRODUCTS VALUES ('P208', 'Microphone', 'USB microphone, desktop', 'Audio', 22.95, 2.5);
INSERT INTO PRODUCTS VALUES ('P210', 'Speakers', '2-speaker, stereo, 10W', 'Audio', 39.99, 7.5);
INSERT INTO PRODUCTS VALUES ('P010', 'Classic Keyboard', 'Black, spill resistant design', 'Input', 21.50, null);
INSERT INTO PRODUCTS VALUES ('P302', 'Inkjet Printer', 'Color and B/W modes, wireless support', 'Print', 89.99, null);
INSERT INTO PRODUCTS VALUES ('P304', 'Laser Printer', 'Color heavy-duty printer', 'Print', 119.50, 25);
INSERT INTO PRODUCTS VALUES ('P312', 'Letter Paper', 'Multipurpose 20lb, 500 sheets', 'Print', 7.50, 5);
INSERT INTO PRODUCTS VALUES ('P046', 'Screen cover', 'Dust protection unit', 'Display', 12.50, null);
INSERT INTO PRODUCTS VALUES ('P215', 'Speakers', 'Mono output, 5W', 'Audio', null, 5.5);
INSERT INTO PRODUCTS VALUES ('P235', 'Audio Pak', 'Speakers and Microphone', 'Audio', 35.95, 10);
INSERT INTO PRODUCTS VALUES ('P322', 'Printer Ink', 'Replacement Cartridges', 'Print', 30, 4);


INSERT INTO SALES VALUES('AX014','01-Mar-2017','Check',  'C002');
INSERT INTO SALES VALUES('CQ951','03-Oct-2016','Cash',   'C005');
INSERT INTO SALES VALUES('BC001','18-Feb-2017','Credit', 'C003');
INSERT INTO SALES VALUES('CB714','21-Sep-2014','PayPal', 'C101');
INSERT INTO SALES VALUES('BM701','04-Mar-2017','GWallet','C002');
INSERT INTO SALES VALUES('LC294','04-Apr-2015','Credit', 'C005');
INSERT INTO SALES VALUES('MB720','04-Oct-2015','PayPal', 'C104');

INSERT INTO SALESLINES VALUES ('AX014','P010',3,19.35);
INSERT INTO SALESLINES VALUES ('AX014','P012',2,14.75);
INSERT INTO SALESLINES VALUES ('AX014','P312',2,7.5);
INSERT INTO SALESLINES VALUES ('AX014','P011',10,40);
INSERT INTO SALESLINES VALUES ('CQ951','P011',4,54.3);
INSERT INTO SALESLINES VALUES ('CQ951','P046',50,11.25);
INSERT INTO SALESLINES VALUES ('BC001','P011',4,40.73);
INSERT INTO SALESLINES VALUES ('BC001','P074',4,8.99);
INSERT INTO SALESLINES VALUES ('BC001','P046',3,12.5);
INSERT INTO SALESLINES VALUES ('BC001','P322',5,30);
INSERT INTO SALESLINES VALUES ('CB714','P011',5,45.25);
INSERT INTO SALESLINES VALUES ('CB714','P302',3,89.99);
INSERT INTO SALESLINES VALUES ('MB720','P011',5,45);
INSERT INTO SALESLINES VALUES ('MB720','P302',3,90);
INSERT INTO SALESLINES VALUES ('BM701','P208',3,32.13);
INSERT INTO SALESLINES VALUES ('LC294','P051',1,103.46);
INSERT INTO SALESLINES VALUES ('LC294','P302',3,89.99);
INSERT INTO SALESLINES VALUES ('LC294','P235',5,43.14);
INSERT INTO SALESLINES VALUES ('LC294','P322',2,33);
INSERT INTO SALESLINES VALUES ('LC294','P312',4,6.75);
INSERT INTO SALESLINES VALUES ('LC294','P010',3,23.65);
INSERT INTO SALESLINES VALUES ('LC294','P074',4,13.99);



commit;

For products with at least two orders (overall, irrespective of payment methods), display the product ID, product name, the number of sales orders paid by cash (heading: Num Cash Sales), the number of sales orders overall (heading: Num Overall Sales)

I have written below query

select sl.prodid, p.pName, a.NumCashSales,count(sl.prodid) as NumOverallSales 
from 
(select count(saleno) as  NumCashSales, saleno as salesno 
    from sales where paymentmethod = 'Cash'
) a 
    Right Outer join saleslines sl 
    Join PRODUCTS p
on sl.prodid = p.prodid 
on sl.saleno= a.salesno

group by sl.prodid 
having count(sl.prodid) >=2; 

it runs on mysql but on oracle it is giving error like

ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: Error at Line: 1 Column: 19

Can anyone please help.

Answer 1

You think that your issue is that the query conversion from MySQL to Oracle fails. You are wrong. Your issue is that your query is flawed (and Oracle mainly tells you so, which MySQL should have done in the first place). Well, admittedly there are some issues a DBMS cannot tell you, such as that the amount of pieces ordered is not the amount of lines in an order.

Here is the query. It is rather simple. You join all tables and aggregate. Only, in order to get the cash sales, you need conditional aggregation. No outer join needed. No subquery needed.

select
  p.prodid,
  p.pname,
  sum(sl.qty) as num_overall_sales,
  sum(case when s.paymentmethod = 'Cash' then sl.qty end) as num_cash_sales
from sales s 
join saleslines sl on sl.saleno = s.saleno
join products p on p.prodid = sl.prodid 
group by p.prodid, p.pname
having count(distinct s.saleno) >= 2;

This query is standard SQL and should work in about every RDBMS. It works in MySQL and it works in Oracle.

As mentioned in my other answer, GROUP BY p.pname is only in the query for Oracle's sake; it is neither required by MySQL nor by the SQL standard.

Answer 2

You group by product ID and want to show the product's name along. This works in MySQL and is defined thus in the SQL standard. The produt name is uniquely identified by the product ID, so there's nothing wong about this. Oracle, however, requires that you group by the product name, too. This is probably the case, because in some situations (not here) it is difficult to determine functional dependence between attributes.

Your first subquery is invalid. You select one line with the record count (which should be count(*) not count(saleno) for readability), but select salesno, too. There can be multiple salesno however, so you arbitrarily pick one. This is possible in MySQL, but it's actually a flaw. In recent MySQL versions, you'd have to use ANY_VALUE(saleno) if you want to do this. In Oracle this is invalid, as it should be according to the SQL standard. Oracle doesn't feature ANY_VALUE, but you can use MIN(saleno) or MAX(saleno) instead. Maybe, however, you rather want to group by saleno instead in order to get a row per saleno?

With only one a record, Oracle would again require you put NumCashSales into GROUP BY. With one a record per saleno you'd even have to apply some aggregation, e.g. SUM(a.NumCashSales) per product.

Then your joins are incorrect. They must be followed by the ON clause immediately, which is not the case in your query. MySQL let's this slip unnoticed and violates the SQL standard here.

So first get your original query straight. Then add pName to GROUP BY to obey Oracle's limitation and you're done.

Answer 3

Oracle implementation of Group by is the Standard one:

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL because the name column in the select list does not appear in the GROUP BY:

For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group.

So this should works

select sl.prodid, p.pName, a.NumCashSales,count(sl.prodid) as 
  NumOverallSales 
from 
  (select count(saleno) as  NumCashSales, saleno as salesno 
     from sales where paymentmethod = 'Cash'
  ) a 
  Right Outer join saleslines sl 
     on sl.saleno= a.salesno
  Join PRODUCTS p  
     on sl.prodid = p.prodid 

 group by sl.prodid,p.pName, a.NumCashSales 
 having count(sl.prodid) >=2;