New SQL creation problem

[Erwin007]

Table reps:
- rep_id
- rep_name
- rep_touroperatorid

Table sales:
- sales_id
- sales_repid
- sales_totaldollars

my query so far:
SELECT reps.rep_name, sum(sales.sales_totaldollars) as total
FROM `sales`
JOIN reps on reps.rep_touroperator_id = '5' 
WHERE (sales.sales_date BETWEEN '$from' AND '$to')
GROUP BY reps.rep_name

and this as code:

$result = mysqli_query($con,$query);
$total = 0;
while($row = mysqli_fetch_assoc($result)){
      $total+=$row['total']


It is working but per rep I get the total of all sales for that period of time instead of the total sales per rep.
I need something like this:
Juan                 3,500
Maria                2,000
Peter                 1,500

But I get this:

Juan                 7,000
Maria                7,000
Peter                 7,000

I hope I gave enough info to solve this.

Thanks

 

[Barand]

You need to join on the rep ids

SELECT reps.rep_name, sum(sales.sales_totaldollars) as total
FROM `sales`
JOIN reps on reps.rep_id = sales.sales_repid
WHERE rep_touroperatorid = '5'  AND
    (sales.sales_date BETWEEN '$from' AND '$to')
GROUP BY reps.rep_name

 

[Erwin007]

Unfortunately it doesn't work.
Only the reps with reps_touroperatorid 5 count.
In my query that part worked.
I do not get any result with your query.

[mac_gyver]

your column names imply that sales.sales_repid contains matching reps.rep_id values. if the query didn't produce any result, that indicates there isn't matching data, either in the join condition or in the where clause. you would need to post an example of the data that the query should match for anyone here to have a chance at helping with the problem.

Edited May 16, 2023 by mac_gyver

[Erwin007]

Hi,

I solved the abovementioned problem but have now another problem but it's kind of complex, let me try:
There are 5 tables in the DB:
reps: rep_id, rep_name, rep_touroperator_id and rep_active
touroperators: touroperator_id, touroperator_name
products: product_id, product_name
sales: 


 

Edited May 16, 2023 by Erwin007
too com plex to explain

[Erwin007]

I have 2 queries:

Query1 =

SELECT reps.rep_name, sum(sales.sales_totaldollars) as total
FROM sales
JOIN reps on reps.rep_id = sales.sales_repid
AND reps.rep_touroperator_id = ‘5’
AND reps.rep_active = 1
WHERE (sales.sales_date BETWEEN ‘$from’ AND ‘$to’)
GROUP BY reps.rep_name

Query2 =

SELECT reps.rep_name, sum(salesdetails.salesdetails_pricedollars) as totalrum
FROM sales
JOIN salesdetails on salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr
AND salesdetails.salesdetails_productid = ‘41’
JOIN reps on reps.rep_id = sales.sales_repid
AND reps.rep_touroperator_id = ‘5’
AND reps.rep_active = 1
WHERE (sales.sales_date BETWEEN ‘$from’ AND ‘$to’)
GROUP BY reps.rep_name

Separately the result is 100%, combined it is not.
Anybody?

 

[Erwin007]

It probably can't be done...

[Barand]

1 hour ago, Erwin007 said:

Separately the result is 100%, combined it is not.

define combined.

We haven't a clue what you are tryng to do, or what you expect, and in which way it isn't doing what you expect.

Without information we cannot help. Your test data too would be good.

[Erwin007]

Ok, I will try again:

This report I need:
REP:                                            Product(41):                              TOTAL SALES:
Cely                                                76                                           206
Heike                                            192                                           502

The Report I get (with my query):
REP:                                            Product(41):                              TOTAL SALES:
Cely                                                76                                           206
Heike                                            192                                           416

This is my query:

SELECT reps.rep_name, sum(sales.sales_totaldollars) as total, sum(salesdetails.salesdetails_pricedollars) as totalrum
FROM `sales`
JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr AND salesdetails.salesdetails_productid =  '41'                                                                                        
JOIN reps ON reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
WHERE (sales.sales_date BETWEEN '$from' AND '$to') 
GROUP BY reps.rep_name";

The error (the missing 86 at total sales for Heike) is that is "skips" the 2 tickets that don't have the product id 41.
The date I choose here is from May 8 till May 8, but it works 100% (just like the GROUP BY part) with other periods, also the part JOIN reps on... works 100%.
Somewhere in this JOIN salesdetails on... is my error.


I hope it's a little bit more clear.

 

Edited May 16, 2023 by Erwin007
more explanation

[Barand]

Better.

Now tell me how you expect me to to load the image of your data into my database to test your query!

[Erwin007]

I have no idea unfortunately.

[Barand]

4 minutes ago, Erwin007 said:

I have no idea unfortunately.

The point is - don't post pictures of data (or code). They are a much use as chocolate teapots.

For data, the preferred format is a data export dump, to make life easier for those trying to help you.

 

[Erwin007]

I have sales.sql and salsdetails.sql...how to upload or send? 

[Barand]

Add ".txt" to the end of the filenames and attach the files

or

copy/paste the contents in code blocks (<> button) as you would php code.

[Erwin007]

sales.sql.txt salesdetails.sql.txt

[Barand]

Which set of data gave you result you posted...

1 hour ago, Erwin007 said:

The Report I get (with my query):
REP:                                            Product(41):                              TOTAL SALES:
Cely                                                76                                           206
Heike                                            192                                           416

Was it

• the data in your picture, or
• the data in those sql files

I created data by typing from the pictures (not a chore I enjoy) but on running your query my results matched neither of those posted - expected/actual

[Erwin007]

I hope you mean this:

Selection from 1st of May till 15 of May.
The amount of 416 must be 502

Edited May 16, 2023 by Erwin007

[Barand]

The 416 appears to be correct.

mysql> SELECT reps.rep_id
    ->       , SUM(sales_totaldollars) as totaldollars
    -> FROM `sales`
    -> JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr
    ->                  AND salesdetails.salesdetails_productid =  '41'                                                                      
    -> JOIN reps ON reps.rep_id = sales.sales_repid
    ->          AND reps.rep_touroperator_id = '5'
    ->          AND reps.rep_active = 1
    -> WHERE sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15'
    -> GROUP BY rep_id;
+--------+--------------+
| rep_id | totaldollars |
+--------+--------------+
|     77 |          206 |
|    259 |          416 |
+--------+--------------+

Why do you think it should be 502?

[Erwin007]

Because US$ 502 is the total sales for Heike.
The 1st columns is the sales for productid 41, the second column is the total sales for that rep in that period.

Attached a report of sales per rep divided in products with the total

[kicken]

Sounds like you want to separate totals.

• The total sales for Product 41
• The overall total sales (for all products)

Is that right?  If so, you'd want to make two queries that will get you each of those totals, then use them as sub-queries in a third query that lists the reps.

[Erwin007]

I have a query to find out the total sales for that rep for that period and it works 100%:
SELECT reps.rep_name, sum(sales.sales_totaldollars) as total
FROM `sales`
JOIN reps on reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
WHERE (sales.sales_date BETWEEN '$from' AND '$to')
GROUP BY reps.rep_name

I have anothe query where I find the total sales of productid 41 for that rep in that period and it also works 100%:
SELECT reps.rep_name, sum(salesdetails.salesdetails_pricedollars) as totalrum
FROM `sales`
JOIN salesdetails on salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr  
JOIN reps on reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
WHERE (sales.sales_date BETWEEN '$from' AND '$to') AND salesdetails.salesdetails_productid = '41'
GROUP BY reps.rep_name

"Combining" these 2 has been a nightmare the last 20hours....

[Erwin007]

2 minutes ago, kicken said:

Sounds like you want to separate totals.

• The total sales for Product 41
• The overall total sales (for all products)

Is that right?  If so, you'd want to make two queries that will get you each of those totals, then use them as sub-queries in a third query that lists the reps.

Yes, I want to know the sales for productid 41 and the total sales for that rep.
I just posted the 2 queries I made and they work flawless, but "combining" them...pppffffttt

 

[kicken]

select 
	reps.rep_name,
    p41Sales.totalrum,
    allSales.total
from reps 
inner join (
	SELECT reps.rep_id, sum(sales.sales_totaldollars) as total
	FROM `sales`
	JOIN reps on reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
	WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15')
	GROUP BY reps.rep_id
) allSales on allSales.rep_id=reps.rep_id
inner join (
	SELECT reps.rep_id, sum(salesdetails.salesdetails_pricedollars) as totalrum
	FROM `sales`
	JOIN salesdetails on salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr  
	JOIN reps on reps.rep_id = sales.sales_repid AND reps.rep_touroperator_id = '5' AND reps.rep_active = 1
	WHERE (sales.sales_date BETWEEN '2023-05-01' AND '2023-05-15') AND salesdetails.salesdetails_productid = '41'
	GROUP BY reps.rep_name
) p41Sales on p41Sales.rep_id=reps.rep_id

Your two independent queries become sub-queries that select the totals you want, grouped by rep ID.

You join the results of those queries then to your reps table by the ID and output the rep details and the totals.

 

[Erwin007]

It almost works.
It doesn't show the reps that sold something other than product id=41. )the amount at product must be 0 but the total sales has an amount.

if you put the from date 2023-04-01 and to-date 2023-04-30 you see 2 more reps (Jose Mendez and Honeydi) which didn't sell productid 41 but they sold some other stuff.

Edited May 17, 2023 by Erwin007

[Erwin007]

Maybe the reps table comes in handy

 

reps.sql.txt