SQL Selecting statement, sum function in where

Question

I have a table with a lot of bills. And I need to select the items from the bills where the bill excedes a price.

I tried this :

SELECT   d.BillNumber
       , SUM(d1.Amount* d1.Price) 
       , d2.Name AS FinalPrice
FROM GEMsc106Antet d LEFT OUTER JOIN GEMsc106Pozitii d1 
ON d1.Luna = d.Luna AND d1.NumarI = d.NumarI 
JOIN GEcProduse d2 ON d2.Cod = d1.CodMaterial 
WHERE YEAR(d.Data) = 2013 
GROUP BY   d.BillNumber , d2.Name 
HAVING SUM(d1.Amount* d1.Price) >= 10000

But this statement seems not to do the trick, as first it selects me only the bill from2013 which is OK, but then I should get all the bills that are greater then 10.000, and I can't use the sum in the where clause and only after that I should GROUP them.

What can I do?

The query seems fine...You have 3 joins...So if at least one join is not verified the query will skip a lot of results...Try to make tests by removing some joins to make sure when things go wrong — ASNAOUI Ayoub
– ASNAOUI Ayoub, Commented Mar 25, 2014 at 9:33
Is the BillNumber unique? If so you won't need a group by and can simply check amount * price in the where clause — Steve Ford
– Steve Ford, Commented Mar 25, 2014 at 11:48

Dmitrii Bychenko · Accepted Answer · 2014-03-25 09:46:25Z

1

Probably, you don't want to cram all the logic into one select. Try with construction:

with data as ( 
       -- Your preliminary data: cheques (?) with bill sum and the actual price
   select d.BillNumber as BillNumber, 
          sum(d1.Amount * d1.Price) as Bill,
          d2.Name as Price
     from GEMsc106Antet d 
            left join
              GEMsc106Pozitii d1 on d1.Luna = d.Luna and d1.NumarI = d.NumarI
            join
              GEcProduse d2 on d2.Cod = d1.CodMaterial 
    where Year(d.Data) = 2013
 group by d.BillNumber, 
          d2.Name)

-- Final query: just use appropriate where condition
select *
  from data
 where Bill > Price

edited Mar 25, 2014 at 9:46

answered Mar 25, 2014 at 9:34

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

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

Comments

Steve Ford · Accepted Answer · 2014-03-25 11:51:09Z

0

This will only work if the BillNumber and d2.Name is unique:

SELECT   d.BillNumber
       , d1.Amount* d1.Price 
       , d2.Name AS FinalPrice
FROM GEMsc106Antet d 
LEFT OUTER JOIN GEMsc106Pozitii d1 
   ON d1.Luna = d.Luna AND d1.NumarI = d.NumarI 
JOIN GEcProduse d2 
   ON d2.Cod = d1.CodMaterial 
WHERE YEAR(d.Data) = 2013 AND (d1.Amount* d1.Price) >= 10000

answered Mar 25, 2014 at 11:51

Steve Ford

7,81323 silver badges42 bronze badges

Comments

CiucaS · Accepted Answer · 2014-03-26 10:33:00Z

0

I manage to solve the problem the query looks like this

Select x.Name
from GECProduse x
join GEMSc106Pozitii p on x.Cod= p.CodMaterial
join (Select d.Month, d.NumarI from GeMsc106Antet d 
    join GEMsc106Pozitii d1 on d.Month= d1.Month and d.NumarI=d1.NumarI
    WHERE YEAR(d.Data) = 2013
    GROUP by d.Numar,d.Month, d.NumarI HAVING sum(d1.Quantity*d1.SellingPrice) >= 10000) p1 on p1.Month=p.Month and p1.NumarI=p.NumarI
GROUP BY x.Name

answered Mar 26, 2014 at 10:33

CiucaS

2,1486 gold badges40 silver badges69 bronze badges

Collectives™ on Stack Overflow

SQL Selecting statement, sum function in where

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related