Problem with query?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Skywalker
Forum Contributor
Posts: 117
Joined: Thu Aug 29, 2002 3:33 am
Location: The Netherlands

Problem with query?

Post by Skywalker »

I need sum the prijses of divrend users.

Sow I have hmm the name Jan and the name Bert
Jan order 2 products and Bert 1.

Jan's total prise must be calculated. and that from Berts two.

I have to 2 tables one tabl is produkten and the other is verkoop.


Table Produkten contains: ProduktID, ProduktNaam, Prijs.
Table Verkoop contains : BestelId,ProduktID,Naam,Aantal.

No I made an extra field with AS that's TotaalPrijs
TotaalPrijs is Prijs*Aantal

No I need to count Totaal Prijs (SUM)Totaal prijs where Naam=Verkoop.Naam

Something like this it has to be. It is a kind off difucult to explane. But please try to help me :roll:
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

Code: Select all

SELECT SUM(TotaalPrijs) FROM Verkoop WHERE naam="BERT"
I think thats what you wanted???
User avatar
haagen
Forum Commoner
Posts: 79
Joined: Thu Jul 11, 2002 3:57 pm
Location: Sweden, Lund

Post by haagen »

Try the following (untested query);

SELECT v.Naam, SUM(v.Aantal*p.Prijs) AS Summa FROM Verkoop v LEFT JOIN Produkten p ON p.ProduktID = v.ProduktID GROUP BY BestelId,Naam;

Try this. I'm not truly sure that the GROUP BY on two columns work, but you may give it a go. The result how ever should be like this:

Name | Summa
Jan | 200
Bert | 345
User avatar
Skywalker
Forum Contributor
Posts: 117
Joined: Thu Aug 29, 2002 3:33 am
Location: The Netherlands

Post by Skywalker »

Ok thx, I'll give it a try :D
User avatar
Skywalker
Forum Contributor
Posts: 117
Joined: Thu Aug 29, 2002 3:33 am
Location: The Netherlands

Post by Skywalker »

It doesn't work Haagen, it brengs only the results of all the record and not the sum of the records wher the name is the samen

Fore example

Name - ProduktName - Prise - How much Products

Jan - Bamischijf - 3 euro - 2
Bert - Hamburger - 2 euro - 1
Jan -Broodje worst - 4 euro -2

No the question is, I want to select 1 time every name, Show how much orders that they made and what the total prise is combiend for every name :D

That is my problem. Now hey only selects al the names and isn't showing the sum of everyname. GOT it :D

Sory mis understanding, my english is a bit <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span>.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

As haagen said but without the BestelId in the group by clause

SELECT v.Naam, SUM(v.Aantal*p.Prijs) AS Summa FROM Verkoop v LEFT JOIN Produkten p ON p.ProduktID = v.ProduktID GROUP BY Naam;

Generally you only need to group on fields that are included in the SELECT part of your query, I assume BestelId is unique for every record that is why it is producing every record in SUMming.
User avatar
Skywalker
Forum Contributor
Posts: 117
Joined: Thu Aug 29, 2002 3:33 am
Location: The Netherlands

Post by Skywalker »

Thx Guys, it workt :D
Pff man that was my holl day work :D I tryed and tryed and finally somebody helped me good :D THX Guys
User avatar
Skywalker
Forum Contributor
Posts: 117
Joined: Thu Aug 29, 2002 3:33 am
Location: The Netherlands

Post by Skywalker »

How can I get this

SELECT Naam,count(*)
FROM verkoop
GROUP BY Naam
HAVING count(*)>0


IN TO THIS


SELECT v.Naam, SUM(v.Aantal*p.Prijs) AS Summa FROM Verkoop v LEFT JOIN Produkten p ON p.ProduktID = v.ProduktID GROUP BY Naam;
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Not sure if I understand you correctly but...

SELECT v.Naam, SUM(v.Aantal*p.Prijs) AS Summa
FROM Verkoop v LEFT JOIN Produkten p ON p.ProduktID = v.ProduktID GROUP BY Naam
HAVING SUM(v.Aantal*p.Prijs) > 0;

perhaps?
User avatar
Skywalker
Forum Contributor
Posts: 117
Joined: Thu Aug 29, 2002 3:33 am
Location: The Netherlands

Post by Skywalker »

No I mean, how can I combine this:

SELECT Naam,count(*)
FROM verkoop
GROUP BY Naam
HAVING count(*)>0

with this

SELECT v.Naam, SUM(v.Aantal*p.Prijs) AS Summa FROM Verkoop v LEFT JOIN Produkten p ON p.ProduktID = v.ProduktID GROUP BY Naam;
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Have you tried

SELECT v.Naam, count(*), SUM(v.Aantal*p.Prijs) AS Summa
FROM Verkoop v LEFT JOIN Produkten p ON p.ProduktID = v.ProduktID GROUP BY Naam;
User avatar
Skywalker
Forum Contributor
Posts: 117
Joined: Thu Aug 29, 2002 3:33 am
Location: The Netherlands

Post by Skywalker »

New problem now I have to show some other stuf, But it is still dificult to do for me. :S

Now I have to show how many of the same product have been order so that means how meny of witch product and in the end, it must show the SUM of TOTAALPRIJS

To make it a bit easier, the tables again.

table 1 Verkoop, table 2 Produkten

table1 colms table2 colms

- BestelID - ProdukctID
- ProductID - NaamProdukt
- Naam - Prijs
- Aantal

Sow, I have to know how much orders have been plased of the same product, It must draw how much and with product.

Then in the end it must count all prises from evrybody So that means the SUM of TOTAALPRIJS remember, i mad in produkten the TotaalPrijs with the as command. I did Prijs*Aantal remember :D

Thx alot guys and specialy mikeq. That's is al. I tryed the holl morning but it still doesn't work.

Greathings SKywalker
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

I would say you would be looking at 2 queries, 1 to do the number of orders per product and another to do the Total Price

Can you just clarify for me

BestelID - OrderID?
Aantal - Quantity?
have to know how much orders have been plased of the same product, It must draw how much and with product
select count(V.BestelID), sum(V.Aantal*P.Prijs), P.NaamProdukt
from Verkoop V, Produkten P
where V.ProductID = P.ProdukctID
group by P.NaamProdukt
Then in the end it must count all prises from evrybody So that means the SUM of TOTAALPRIJS
I will assume you mean the Total Price for all products bought

select sum(V.Aantal*P.Prijs) TotalPrijs
from Verkoop V, Produkten P
where V.ProductID = P.ProdukctID

So this will give you the Total spend for all products ordered.


i.e.

1st query gives
Product|Number Orders|Total Spend
Product One|12|500.00
Product Two|2|123.23
Product Three|15|45.65

Then 2nd query would give
TotalPrijs
668.88
User avatar
Skywalker
Forum Contributor
Posts: 117
Joined: Thu Aug 29, 2002 3:33 am
Location: The Netherlands

Post by Skywalker »

Sory my mistace, it works ok now :D
Post Reply