Say I have the 3 tables representing sales of certain items (each row representing a sale)

I want to write a single SQL query which outputs a row for each salesman, and each column showing the number of sales a salesman had for each item:

Desired Output
Salesman # Washer Sales # Nut Sales # Screw Sales
JD 2 1 1
MR 1 0 1
LK 0 1 0
NR 0 1 1

I thought of unioning all the tables together (adding in a string value column called Tablename on each table before to say nuts,washers,scews), but then it seems like I would have to have a WHERE condition of sorts in the SELECT line, and each entry in the SELECT statement would be like count(DISTINCT ID) WHERE Tablename = nuts, Count(DISTINCT ID) WHERE etc which wouldn't work.I also considered using a Case statement but I couldn't see how to make that work.

My actually use of this query is much more complicated than this, but I think the concept should be the same. The example tables are below, thanks for any ideas.


washers

ID Customer Salesman Date Quantity
2 Walkers JD 2005-02-05 6
3 Briggs MR 2006-01-01 7
4 Lowes JD 2006-01-04 3


nuts

ID Customer Salesman Date Quantity
3 Briggs LK 2005-08-24 8
4 Depot JD 2005-08-28 22
5 Lowes NR 2006-01-01 10


screws

ID Customer Salesman Date Quantity
5 Depot NR 2005-03-24 4
6 Depot JD 2005-06-28 32
7 Lowes MR 2006-02-01 13



[Edited on March 29, 2006 at 10:01 AM. Reason : tables]

3/29/2006 9:59:45 AM

select w.Salesman, sum(w.Quantity), sum(n.Quantity), sum(s.Quantity) from washers as w join nuts as n on n.Salesman = w.Salesman join screws as s on s.Salesman = w.Salesman group by w.Salesman


may be a little different depending on which system you're using

[Edited on March 29, 2006 at 10:22 AM. Reason : whoops]

3/29/2006 10:21:25 AM

I'm using MSSQL, but I don't think a join will work since the salesmen who sell washers are not necessarily the same salesman who sell nuts etc.

[Edited on March 29, 2006 at 10:32 AM. Reason : ]

3/29/2006 10:28:17 AM

in that case, you need another table which lists just salesman. you should have one anyway, the washer/nut/screw tables should be referenced to it.

join the washer/nut/screw tables onto the salesman table, use left join instead of join, and there is your solution

you could also use CASE with the sum()'s so that it will return 0 instead of NULL when a salesman hasn't sold any particular product

[Edited on March 29, 2006 at 10:36 AM. Reason : and]

3/29/2006 10:35:38 AM

when i join all of my sales tables (around 30 total) to the salesman tables (via left outer join), that doesn't work so well. when i ran the query it ran for over 20 minutes before i stopped it, and upon stopping i noticed it had created over 2 million rows in my result set...not quite what i need to be happening. ^,^^ might work for really small tables like the ones i listed but my actual tables are much, much larger (some have over 40K rows)

any ideas that solutions that might be more efficent?

3/29/2006 3:18:48 PM

Let me get this straight. You have 3 tables which contain items sold with a salesman ID associated to each sale.

1) like [user]mattc[user] said, you should have a table with the salesman name and ID, where the ID is what links each salesman to each sale no matter what table the sale is in. Is this the situation? If not what is?

2) What is the purpose of this? Salesman Report for a manager?

3/29/2006 5:19:12 PM

the way i see it, you need at least the following tables:
salesmen: salesmen names, rank, whatever
products: products, price, sizes, inventory
customers: customer name, address

sales: salesmanID, customerID, date
productSales: salesID, productID, quantity

something like that.... with a similar arrangement, regardless of the number of records you have, the requested info should be trivial

3/29/2006 5:48:49 PM

Raige:
1) correct, i have a salesman table
2) see below


The purpose of this report is to see how many sales events of each item, each salesman had (i don't care about how many washers they sold, i need to know how times each salesman sold a batch of washers etc).

agentlion: The table configuration is listed above, and isn't going to change. As I said my tables are more complicated and much larger but the concept of this query (counting the number of sales events of each item for each salesman) directly applies. I know how to do this query for a single table, its doing it for over 30 tables in a single query thats throwing me off.


[Edited on March 29, 2006 at 8:58 PM. Reason : ]

3/29/2006 8:56:48 PM

your answer will involve a subselect.

3/29/2006 9:22:07 PM

yeah so far the only way i've been able to approach this is with a shit ton of subqueries. i was hoping there was an easier way

3/29/2006 10:06:47 PM

I used to query multiple tables siultaneously in access doing something like:

SELECT [salesman.salesman], [washers.quantity], [screws.quantity], [nuts.quantity] FROM [salesman],[washers],[nuts],[screws] WHERE ([salesman.salesman] = [washers.salesman] and [salesman.salesman] = [nuts.salesman] and [salesman.salesman] = [screws.salesman])



I don't remember if it was exactly like that, but something close to that. That should return something like:

[salesman.salesman] [washers.quantity] [screws.quantity] [nuts.quantity]
JD 6 0 0
JD 3 0 0
JD 0 32 0
JD 0 0 22
MR 7 0 0
MR 0 13 0
NR 0 4 0
NR 0 0 10
LK 0 0 8



I never used SUM() but if my guess is correct then changing [washers.quantity] to SUM([washers.quantity]) and so forth between SELECT and FROM, you should get:

JD 9 32 22
MR 7 13 0
NR 0 4 10
LK 0 0 8

3/29/2006 11:27:32 PM

^ It looks lke you're reporting the quantity of each item sold. Check 3 posts up for a clarification of what I need to report on.

3/29/2006 11:45:01 PM

i know you said table configuration won't change, but is there a reason you didn't make one table for all your sales types

id  product_id customer_id salesman_id date quantity
1   1          1           1           ?    2
2   1          2           1           ?    5
3   2          3           2           ?    6

id product_name
1  washer
2  nut
3  screw

id customer_name
1  Walkers
2  Briggs
3  Lowes

3/30/2006 9:42:46 AM

I'd honestly just make the master table in a subquery and just select the different counts where salesman = ....

have you thought about maybe creating a custom view?

3/30/2006 11:29:07 AM

Quote :

"i know you said table configuration won't change, but is there a reason you didn't make one table for all your sales types "

because the actual tables i'm using are much larger and more complicated than the ones i posted. each table (we have over 30) has 20 or more columns detailing the exact "sale" in question.

we're actually a human services organization, and each "sale" is a service delivered to a person and each "salesman" is a human services worker but the concepts are exactly the same no matter when I call them. I just pickes sales stuff because i thought it would be easier to work with.

I might make a view for this particular report in the future, but until then I'm using subqueries.

3/30/2006 11:38:27 AM

haha so glad i dont have to do this crap anymore

3/30/2006 12:16:18 PM