Page 1 of 1

Problem with query?

Posted: Tue Sep 03, 2002 4:29 am
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:

Posted: Tue Sep 03, 2002 6:03 am
by Wayne

Code: Select all

SELECT SUM(TotaalPrijs) FROM Verkoop WHERE naam="BERT"
I think thats what you wanted???

Posted: Tue Sep 03, 2002 6:04 am
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

Posted: Tue Sep 03, 2002 6:11 am
by Skywalker
Ok thx, I'll give it a try :D

Posted: Tue Sep 03, 2002 6:24 am
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>.

Posted: Tue Sep 03, 2002 7:31 am
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.

Posted: Tue Sep 03, 2002 7:54 am
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

Posted: Tue Sep 03, 2002 8:17 am
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;

Posted: Tue Sep 03, 2002 9:56 am
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?

Posted: Wed Sep 04, 2002 2:47 am
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;

Posted: Wed Sep 04, 2002 3:22 am
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;

Posted: Wed Sep 04, 2002 3:58 am
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

Posted: Wed Sep 04, 2002 8:06 am
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

Posted: Wed Sep 04, 2002 8:32 am
by Skywalker
Sory my mistace, it works ok now :D