473,320 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Need help with a query

Suppose I have two tables:

CREATE TABLE Tab1 (
[A1] [int] NOT NULL,
[A2] [int] NOT NULL,
[B1] [int] NOT NULL,
[B2] [int] NOT NULL)

CREATE TABLE Tab2 (
[A1] [int] NOT NULL,
[A2] [int] NOT NULL,
[B1] [int] NOT NULL,
[B2] [int] NOT NULL)

I need to sum B1 and B2 values grouped by A1 and A2 values from Tab1
united with the portion of Tab2 for which the A1 and A2 values do not
exist in Tab1. Is there a nice T-SQL way to put it into one and
possibly small query?
Jul 20 '05 #1
5 1326
On 20 Oct 2004 02:22:58 -0700, Alexander Korovyev wrote:
Suppose I have two tables:

CREATE TABLE Tab1 (
[A1] [int] NOT NULL,
[A2] [int] NOT NULL,
[B1] [int] NOT NULL,
[B2] [int] NOT NULL)

CREATE TABLE Tab2 (
[A1] [int] NOT NULL,
[A2] [int] NOT NULL,
[B1] [int] NOT NULL,
[B2] [int] NOT NULL)

I need to sum B1 and B2 values grouped by A1 and A2 values from Tab1
united with the portion of Tab2 for which the A1 and A2 values do not
exist in Tab1. Is there a nice T-SQL way to put it into one and
possibly small query?


Hi Alexander,

From the narrative, it's hard to understand what you want. Please provide
some illustrative sample data (in the form of INSERT statements, so that I
can copy and paste for testing purposes) and the output you expect for
that sample data.

Also, provide some explanation about the real world problem you're trying
to solve. This problem looks like a homework assignment; if it is I'll
gladly give you some pointers, but not a complete solution. If it's a real
world problem you're facing in your job, I'll be more tempted to give a
complete solution.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2
Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<r2********************************@4ax.com>. ..
On 20 Oct 2004 02:22:58 -0700, Alexander Korovyev wrote:
Suppose I have two tables:

CREATE TABLE Tab1 (
[A1] [int] NOT NULL,
[A2] [int] NOT NULL,
[B1] [int] NOT NULL,
[B2] [int] NOT NULL)

CREATE TABLE Tab2 (
[A1] [int] NOT NULL,
[A2] [int] NOT NULL,
[B1] [int] NOT NULL,
[B2] [int] NOT NULL)

I need to sum B1 and B2 values grouped by A1 and A2 values from Tab1
united with the portion of Tab2 for which the A1 and A2 values do not
exist in Tab1. Is there a nice T-SQL way to put it into one and
possibly small query?


Hi Alexander,

From the narrative, it's hard to understand what you want. Please provide
some illustrative sample data (in the form of INSERT statements, so that I
can copy and paste for testing purposes) and the output you expect for
that sample data.

Also, provide some explanation about the real world problem you're trying
to solve. This problem looks like a homework assignment; if it is I'll
gladly give you some pointers, but not a complete solution. If it's a real
world problem you're facing in your job, I'll be more tempted to give a
complete solution.


Hello Hugo,

Here is my illustrative sample data:

INSERT INTO Tab1 VALUES(0, 0, 10, 20)
INSERT INTO Tab1 VALUES(0, 1, 15, -5)
INSERT INTO Tab1 VALUES(0, 1, 25, 15)
INSERT INTO Tab1 VALUES(1, 0, 35, 10)

INSERT INTO Tab2 VALUES(0, 1, 40, 0)
INSERT INTO Tab2 VALUES(1, 0, 15, 15)
INSERT INTO Tab2 VALUES(1, 1, 25, 30)

The following is what the result should look like:

0, 0, 10, 20
0, 1, 40, 10
1, 0, 35, 10
1, 1, 25, 30

I have come up with this perverted code so far:

SELECT A1, A2, SUM(B1), SUM(B2) FROM (
SELECT A1, A2, SUM(B1) as B1, SUM(B2) as B2, 1 as p FROM Tab1
GROUP BY A1, A2
UNION
SELECT A1, A2, SUM(B1) as B1, SUM(B2) as B2, 0 as p FROM Tab2
GROUP BY A1, A2
) AS t GROUP BY A1, A2 HAVING SUM(p)=0
UNION SELECT A1, A2, SUM(B1) as B1, SUM(B2) as B2 FROM Tab1 GROUP BY
A1, A2 ORDER BY A1, A2

It shows bad performance and looks ugly. There certainly must be a
better (=more concise) way of expressing it. Thank you.

P.S. Since you asked, this is not a homework problem.. but for the
sake of sparing you the full detail (which is long and not very
interesting) it can be considered so :) (i.e. I don't mind pointers
only).
Jul 20 '05 #3
On 20 Oct 2004 12:24:33 -0700, Alexander Korovyev wrote:

(snip)
I have come up with this perverted code so far:

SELECT A1, A2, SUM(B1), SUM(B2) FROM (
SELECT A1, A2, SUM(B1) as B1, SUM(B2) as B2, 1 as p FROM Tab1
GROUP BY A1, A2
UNION
SELECT A1, A2, SUM(B1) as B1, SUM(B2) as B2, 0 as p FROM Tab2
GROUP BY A1, A2
) AS t GROUP BY A1, A2 HAVING SUM(p)=0
UNION SELECT A1, A2, SUM(B1) as B1, SUM(B2) as B2 FROM Tab1 GROUP BY
A1, A2 ORDER BY A1, A2

It shows bad performance and looks ugly. There certainly must be a
better (=more concise) way of expressing it. Thank you.


Hi Alexander,

I've got two alternatives that look better and have a "cleaner" execution
plan, but show worse performance on the limited set of data you posted
here. Try them against your database to see if they start behaving better
when there's more data to work on:

SELECT A1, A2, SUM(B1), SUM(B2)
FROM Tab1
GROUP BY A1, A2
UNION ALL
SELECT A1, A2, SUM(B1), SUM(B2)
FROM Tab2
WHERE NOT EXISTS
(SELECT *
FROM Tab1
WHERE Tab1.A1 = Tab2.A1
AND Tab1.A2 = Tab2.A2)
GROUP BY A1, A2

or

SELECT A1, A2, SUM(B1), SUM(B2)
FROM Tab1
GROUP BY A1, A2
UNION ALL
SELECT Tab2.A1, Tab2.A2, SUM(Tab2.B1), SUM(Tab2.B2)
FROM Tab2
LEFT JOIN Tab1
ON Tab1.A1 = Tab2.A1
AND Tab1.A2 = Tab2.A2
WHERE Tab1.A1 IS NULL
GROUP BY Tab2.A1, Tab2.A2
Another approach gave ma a query that is (in my opinion) not so ugly as
yours, but uglier than my first two, but that gives better performance (on
your 7-row testset) than all others is this one:

SELECT COALESCE(t1.A1, t2.A1), COALESCE(t1.A2, t2.A2),
COALESCE(t1.B1, t2.B1), COALESCE(t1.B2, t2.B2)
FROM (SELECT A1, A2, SUM(B1) AS B1, SUM(B2) AS B2
FROM Tab2
GROUP BY A1, A2) AS t2
FULL JOIN (SELECT A1, A2, SUM(B1) AS B1, SUM(B2) AS B2
FROM Tab1
GROUP BY A1, A2) AS t1
ON t1.A1 = t2.A1
AND t1.A2 = t2.A2

A final hint: if you do keep your version of the query, change UNION to
UNIOAN ALL. It should not change the results, but UNION ALL is generally
faster then UNION (no removal of duplicates needed - might save you a sort
and will definitely save you some processing logic)
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #4
On 20 Oct 2004 12:24:33 -0700, Alexander Korovyev wrote:
It shows bad performance and (...)


Hi Alexander,

Forgot to add this on my previous reply:

If all alternatives perform bad as well, you probably need to have a look
at the indexes available for the tables used in your query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #5
Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<t4********************************@4ax.com>. ..
On 20 Oct 2004 12:24:33 -0700, Alexander Korovyev wrote:

(snip)
I have come up with this perverted code so far:

SELECT A1, A2, SUM(B1), SUM(B2) FROM (
SELECT A1, A2, SUM(B1) as B1, SUM(B2) as B2, 1 as p FROM Tab1
GROUP BY A1, A2
UNION
SELECT A1, A2, SUM(B1) as B1, SUM(B2) as B2, 0 as p FROM Tab2
GROUP BY A1, A2
) AS t GROUP BY A1, A2 HAVING SUM(p)=0
UNION SELECT A1, A2, SUM(B1) as B1, SUM(B2) as B2 FROM Tab1 GROUP BY
A1, A2 ORDER BY A1, A2

It shows bad performance and looks ugly. There certainly must be a
better (=more concise) way of expressing it. Thank you.


Hi Alexander,

I've got two alternatives that look better and have a "cleaner" execution
plan, but show worse performance on the limited set of data you posted
here. Try them against your database to see if they start behaving better
when there's more data to work on:

SELECT A1, A2, SUM(B1), SUM(B2)
FROM Tab1
GROUP BY A1, A2
UNION ALL
SELECT A1, A2, SUM(B1), SUM(B2)
FROM Tab2
WHERE NOT EXISTS
(SELECT *
FROM Tab1
WHERE Tab1.A1 = Tab2.A1
AND Tab1.A2 = Tab2.A2)
GROUP BY A1, A2

or

SELECT A1, A2, SUM(B1), SUM(B2)
FROM Tab1
GROUP BY A1, A2
UNION ALL
SELECT Tab2.A1, Tab2.A2, SUM(Tab2.B1), SUM(Tab2.B2)
FROM Tab2
LEFT JOIN Tab1
ON Tab1.A1 = Tab2.A1
AND Tab1.A2 = Tab2.A2
WHERE Tab1.A1 IS NULL
GROUP BY Tab2.A1, Tab2.A2
Another approach gave ma a query that is (in my opinion) not so ugly as
yours, but uglier than my first two, but that gives better performance (on
your 7-row testset) than all others is this one:

SELECT COALESCE(t1.A1, t2.A1), COALESCE(t1.A2, t2.A2),
COALESCE(t1.B1, t2.B1), COALESCE(t1.B2, t2.B2)
FROM (SELECT A1, A2, SUM(B1) AS B1, SUM(B2) AS B2
FROM Tab2
GROUP BY A1, A2) AS t2
FULL JOIN (SELECT A1, A2, SUM(B1) AS B1, SUM(B2) AS B2
FROM Tab1
GROUP BY A1, A2) AS t1
ON t1.A1 = t2.A1
AND t1.A2 = t2.A2

A final hint: if you do keep your version of the query, change UNION to
UNIOAN ALL. It should not change the results, but UNION ALL is generally
faster then UNION (no removal of duplicates needed - might save you a sort
and will definitely save you some processing logic)
Best, Hugo


Thanks a lot!
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: lawrence | last post by:
I've been bad about documentation so far but I'm going to try to be better. I've mostly worked alone so I'm the only one, so far, who's suffered from my bad habits. But I'd like other programmers...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
6
by: paii | last post by:
I have a table that stores job milestone dates. The 2 milestones I am interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18. All jobs have TypeID 1 only some jobs have TypeID 18....
3
by: pw | last post by:
Hi, I am having a mental block trying to figure out how to code this. Two tables: "tblQuestions" (fields = quesnum, questype, question) "tblAnswers" (fields = clientnum, quesnum, questype,...
7
by: K. Crothers | last post by:
I administer a mechanical engineering database. I need to build a query which uses the results from a subquery as its input or criterion. I am attempting to find all of the component parts of...
3
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems...
0
by: ward | last post by:
Greetings. Ok, I admit it, I bit off a bit more than I can chew. I need to complete this "Generate Report" page for my employer and I'm a little over my head. I could use some additional...
10
by: L. R. Du Broff | last post by:
I own a small business. Need to track a few hundred pieces of rental equipment that can be in any of a few dozen locations. I'm an old-time C language programmer (UNIX environment). If the only...
7
by: Rnykster | last post by:
I know a little about Access and have made several single table databases. Been struggling for about a month to do a multiple table database with no success. Help! There are two tables. First...
3
by: pbd22 | last post by:
Hi. I need some help with structuring my query strings. I have a form with a search bar and some links. Each link is a search type (such as "community"). The HREF for the link's anchor looks...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.