Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

query and sub query

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
NiceMan331
Asked:
NiceMan331
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
PortletPaulCommented:
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
 
NiceMan331Author Commented:
both solution are correct , but the code of portlpal is easier for me
thanx
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now