sql sum

Desbrina

i currently have this sql code

SELECT f.ID, f.name, l.name as location, 
SUM( s.created ) AS produced,
SUM( s.amount ) AS total, 
(SUM( s.created ) / SUM( s.amount ) *100) AS percent
FROM factories AS f
LEFT JOIN stockf AS s ON f.ID = s.factoryid
LEFT JOIN locations AS l ON f.location = l.ID
WHERE (f.owner =".clean($_COOKIE['toptycoons']['i'])." AND f.approved = 1)
GROUP BY f.ID
ORDER BY f.name ASC

which does almost what i want it to do, lists the factory id, name, location, the total items produced, total ordered and what percentage has been completed

ID || name || location || produced || total || percent
1 || fact 1 || loc 1 || 124 || 125 || 99
2 || fact 2 || loc 1 || NULL || NULL || NULL

What i want is it to not include the completed stock (so if s.completed =1)

i tried changing part of the sql to

WHERE (f.owner =".clean($_COOKIE['toptycoons']['i'])." AND f.approved = 1 AND s.completed = 0)

but it then ignores the second factory (fact 2)

so it show it like this
ID || name || location || produced || total || percent
1 || fact 1 || loc 1 || 24 || 25 || 96
2 || fact 2 || loc 1 || NULL || NULL || NULL

is it possible to change the sql to do this and how would i?

Roger_Ramjet

Your basic problem here is that your database design is wrong. Yes it may work, and many would disagree with my saying it is wrong. Thing is, it is wrong because you are having trouble getting out the results that you want.

Desbrina

how would you suggest i do it?

Roger_Ramjet

Well you need to normalise your data. Google for that if you do not know what normalisation and Third Normal Form means - load of tutorials out there.

Now I would need to know a lot more about your db and the processes it serves before I could recommend the database schema required, so that is out. All I wanted to point out is that if you have serious problems extracting the required data from a database then the database design is wrong. Some people, including the fools who developed the databases that I inherited at my present job, manage to design databases that you can input data into and then never extract it from accurately again, let alone extract any sort of management information.

So learn how to normalise your data and apply the principles. The fact that it might mean major redesign is not any sort of reason not to do so.

Desbrina

I'm not too bothered about major redesigning since my sites not finished yet.

One possibility i've though of is to have 2 tables, one that holds all completed stock and another that holds non completed stock

What i'm designing is an online game of sorts. Your the manager of a business and are in charge of factories, warehouses and stores.

I had already normalized the database (or so i'd thought, but i only noticed this problem after testing it)

Lars_Berg

Move the criteria from the where clause to the join clause, i.e.

select f.ID, 
       f.name, 
       l.name as location, 
       sum(s.created) AS produced,
       sum(s.amount) AS total, 
       sum(s.created)/sum(s.amount)*100 as percent
  from factories as f
  left join stockf as s on f.ID = s.factoryid
   and s.completed = 0
  left join locations as l on f.location = l.ID
 where f.owner = ".clean($_COOKIE['toptycoons']['i'])." 
   and f.approved = 1
 group by f.ID
 order by f.name ASC

The conditions in the where clause are evaluated after the left join and will thus remove any non-matching records from the right table in the outrer join.

Desbrina

wow, thanks. i didn't realised you could use and in a join

Weedpacket

You can use any expression that evaluates to a boolean.