Join vs Count vs Sum problem 1

[graabein]

Hi, I'm looking to join columns from two tables A and S.

A: id (int) / d (datetime) / ant (int)
S: id (int) / d (datetime) / res (varchar) / el (int)

Table A holds the sales number for a certain department for a certain day.

Table S consists of complaints to a purchase where S.res is the problem and S.el being the number of tries before a complaint was answered. S.id and S.d equals that of table A.

Now I wanna join A and S to a table T that sums up each year for each department, with the following columns..:

T: id (int) the department / sales (int) total number of sales for the department / compl (int) total number of complaints / tries (int) total number of tries.

I already have the following SQL statement (SQL Server):

SELECT A.id AS dep, SUM(A.ant) AS sales, COUNT(S.res) AS compl, SUM(S.el) AS tries FROM A LEFT JOIN S ON A.id=S.id AND A.d=S.d WHERE YEAR(A.d)=2002 GROUP BY A.id;

The sales-column is counted more than one time if there are more than one complaint to the department a certain day. I have no idea how to combat this...

Hope I made the question understandable and that somebody has the solution!

Cheers!

 

[angiole]

Hello graabein,

There are a couple of things that are not clear:

For Table S, is el the total number of tries as a summary value, or is each try listed here?

For Table T, if this table holds the department / year values, where is the year identifying column?

What is in Table T that can't be computed from Table A and S?

AA 8~)

 

[graabein]

Hello,

S.el is the total number of tries for that one complaint to get through. The complaint description is stored in S.res. It is a summary value.

I extract the year from the date variable S.d with the YEAR function.

I need to join A and S to a table T because I need the data in a (Crystal) report. Everything is fine except T.sales when there are more than one complaint for the same date/department combination. The A.ant gets summarized more than once when this happens.

Example, for d=10/09/02 and id=1 (in both A and S...):

A.ant = 20
S.res = "hunky" / S.el = 1
S.res = "dory" / S.el = 3

T looks like this:

id=1 / sales=40 / compl=2 / tries=4

But T.sales is supposed to be 20! I'm sure it's a simple solution for this, I just can't figure it out. There's been a while since I programmed SQL statements...

 

[angiole]

Try This:

SELECT A.id AS dep,
A.ant AS sales,
COUNT(S.res) AS compl,
SUM(S.el) AS tries
FROM A LEFT JOIN S ON A.id=S.id AND A.d=S.d WHERE YEAR(A.d)=2002
GROUP BY A.id,
A.Ant; AA 8~)

 

[graabein]

Hello again,

I'm afraid that didn't quite do it. It worked perfectly on the example I posted, but varying amount of sales have been filed for the same departments.

When I used your statement it spread out the table for each different combination of id and sales instead of grouping them once for each dep.

Here is what I got when I ran the statement:

id / sales / compl / tries
1 / 3 / 0 / null
1 / 4 / 2 / 1
1 / 5 / 2 / 1
1 / 6 / 1 / 0
1 / 7 / 1 / 0

What I want is:

id / sales / compl / tries
1 / 29 / 6 / 2

And the tables have the following data:

A.id / d / ant
1 / 26.09.02 / 7
1 / 27.09.02 / 4
1 / 01.10.02 / 6
1 / 02.10.02 / 5
1 / 04.10.02 / 3
1 / 09.10.02 / 4

S.id / d / res / el
1 / 26.09.02 / "21" / 0
1 / 27.09.02 / "16" / 1
1 / 01.10.02 / "25" / 0
1 / 02.10.02 / "05" / 0
1 / 02.10.02 / "14" / 1
1 / 09.10.02 / "10" / 0

 

[barbarul2001]

what about this one?

SELECT A.id AS dep, SUM(distinct A.ant) AS sales, COUNT(S.res) AS compl, SUM(S.el) AS tries
FROM A LEFT JOIN S ON A.id=S.id AND A.d=S.d
WHERE YEAR(A.d)=2002 GROUP BY A.id; senior rdbms specialist

 

[angiole]

According to the data supplied in the4 sample tables, the will produce the one line result you're expecting:

SELECT A.id AS dep,
sum(A.ant) AS sales,
COUNT(S.res) AS compl,
SUM(S.el) AS tries
FROM A LEFT JOIN S ON A.id=S.id AND A.d=S.d WHERE YEAR(A.d)=2002
GROUP BY A.id

Which is of course the query that you supplied with the problem...

I don't think the sample data/report you provided in your last post correctly illustrate your problem. AA 8~)

 

[graabein]

The DISTINCT in front of a.Ant won't work because I want to summarize all sales, not just each distinct number. Say I had three days of a sale of 20 units, with DISTINCT I only get 20, not 60 like I want.

Let me post anoter data sample...

With the first query I get this table:

T.id / sales / compl / tries
1 / 40 / 7 / 2
2 / 279 / 4 / 0
4 / 80 / 4 / 3

What I want is this:

T.id / sales / compl / tries
1 / 35 / 7 / 2
2 / 252 / 4 / 0
4 / 20 / 4 / 3

Contents of table A:

A.d / id / ant
1 26.09.02 7
1 27.09.02 4
1 01.10.02 6
1 02.10.02 5
1 04.10.02 3
1 09.10.02 4
1 10.10.02 6

2 26.09.02 19
2 27.09.02 20
2 30.09.02 23
2 01.10.02 27
2 02.10.02 24
2 03.10.02 22
2 04.10.02 14
2 07.10.02 20
2 08.10.02 18
2 09.10.02 23
2 10.10.02 27
2 11.10.02 15

4 09.09.02 20

And of table S:

S.id / d / res / el
1 26.09.02 21 0
1 27.09.02 16 1
1 01.10.02 25 0
1 02.10.02 5 0
1 02.10.02 14 1
1 09.10.02 10 0
1 10.10.02 8 0

2 01.10.02 22 0
2 01.10.02 2 0
2 07.10.02 22 0
2 10.10.02 25 0

4 09.09.02 14 2
4 09.09.02 4 1
4 09.09.02 14 0
4 09.09.02 15 0

You see from table A I want to summarize ant for each department. From table S I want the number of complaints reported (one for each record) with the total number of tries (varies from complaint to complaint).

The problem occurs when I have more than one complaint for a certain day -- see 02.10.02 for dep 1, 01.10.02 for dep 2 and 09.09.02 for dep 4 -- then this days sale gets added several times instead of just once.

Dep 1 should have a sale of 35, but gets 40 because of two complaints the 02.10.02 when I had a sale of 5. An extra 5 is added.

Accordingly for dep 2: 2x 01.10.02 with a sale of 27 equals 279 - 252 = 27.

Dep 4: 4x 09.09.02 and a sale of 20 is three times too many..60, 80 - 20 = 60.

I know where the error lies but not how to adjust the query!

 

[barbarul2001]

What about this little one ?


select TT1.*,compl,tries from
(
SELECT A.id AS dep,
sum(A.ant) AS sales,
FROM A
WHERE YEAR(A.d)=2002
GROUP BY A.id
) tt.1 LEFT JOIN
(
select S.id AS dep
,COUNT(S.res) AS compl
,SUM(S.el) AS tries
FROM S
WHERE YEAR(s.d)=2002
GROUP BY s.id
) TT2 ON TT1.dep=TT2.dep
senior rdbms specialist

 

[graabein]

Nice one!

That last one with TT1 and TT2 did the trick. Thanks for the input guys, you really helped me out!