Solved

query and sub query

Posted on 2014-07-22
4
199 Views
Last Modified: 2014-07-23
hello
i have a view sale_tran , it contain data for sales transactions
i want to retrieve a report to give me the following reslut :
date , day_name , total_sales , specific_period_sales (launch or dinner)
i wrote this sql

select a.dob,a.dname,
(select sum(b.gamt) 
from sale_item b where b.dob = a.dob and b.unit = a.unit and b.dname = a.dname) all_sales,
(select sum(c.gamt) 
from sale_item c where c.dob = a.dob
and c.unit = a.unit
and c.period = 2
 and c.dname = a.dname
) noon_sales
from sale_item a
where a.dob > = '07-aug-13' and a.dob <='28-jun-14' 
and a.unit = '2001'
 and a.dname IN ('Fri','Sat')
GROUP BY a.DOB,a.dname
order by a.dob;

Open in new window


but it return not group expression at line 2
0
Comment
Question by:NiceMan331
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40211298
and b.unit = a.unit
in the subquery raises this issue...

so, you need to add the unit column into the group by, or remove it from the conditions in the subqueries
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 40211300
and as unit seems to be a fixed value, adding it to the group by seems the simplest here:
select a.dob,a.dname,
(select sum(b.gamt) 
from sale_item b where b.dob = a.dob and b.unit = a.unit and b.dname = a.dname) all_sales,
(select sum(c.gamt) 
from sale_item c where c.dob = a.dob
and c.unit = a.unit
and c.period = 2
 and c.dname = a.dname
) noon_sales
from sale_item a
where a.dob > = '07-aug-13' and a.dob <='28-jun-14' 
and a.unit = '2001'
 and a.dname IN ('Fri','Sat')
GROUP BY a.DOB,a.dname, a.unit
order by a.dob;
                                  

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
ID: 40214169
everything comes from the one table (or view) so there is no need for the correlated subqueries that I can see
SELECT
      a.dob
    , a.dname
    , SUM(a.gamt)                              AS all_sales
    , SUM(CASE
            WHEN a.period = 2 THEN a.gamt END) AS noon_sales
FROM sale_item a
WHERE a.dob >= '07-aug-13'
      AND a.dob <= '28-jun-14'
      AND a.unit = '2001'
      AND a.dname IN ('Fri', 'Sat')
GROUP BY
      a.DOB
    , a.dname
ORDER BY
      a.dob;

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 40214884
both solution are correct , but the code of portlpal is easier for me
thanx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question