Solved

query and sub query

Posted on 2014-07-22
4
201 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

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 143

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

751 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