Table variables and joins

Hello All,

I have this table:

CREATE TABLE [dbo].[AMS](
[AMSFGGID] [int] IDENTITY(1,1) NOT NULL,
[AMSDESCRIPTION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

with these values:

INSERT INTO AMS (AMSDESCRIPTION)
VALUES ('TEST DESC 1')
INSERT INTO AMS (AMSDESCRIPTION)
VALUES ('TEST DESC 2')

I also have a script which creates a table variable:

DECLARE @RESULTS Table
(
ROWID INT IDENTITY(1,1),
AMSFGGID INT,
QTYSOLD FLOAT
)
INSERT @RESULTS
(
AMSFGGID,
QTYSOLD
)
VALUES
(
1, 300
)
INSERT @RESULTS
(
AMSFGGID,
QTYSOLD
)
VALUES
(
1, 700
)

I'm trying to get construct a join which will sum the quantities sold
for each AMS record, something like:

SELECT
A.AMSFGGID, S.TotalSales
FROM
AMS A
INNER JOIN
(SELECT
AMSFGGID, SUM(QTYSOLD) as TotalSales
FROM
@RESULTS
GROUP BY
AMSFGGID) S
ON
A.AMSFGGID = S.AMSFGGID

Pointers appreciated!

Re: Table variables and joins

I am not sure I understand this correctly, as you query will actually
produce the summary quantity. If you mean to show summary quantity even for
items that do not have quantity in the results table, then you can use left
join:

SELECT A.amsfggid,
SUM(COALESCE(R.qtysold, 0)) AS TotalSales
FROM AMS AS A
LEFT OUTER JOIN @Results AS R
ON A.amsfggid = R.amsfggid
GROUP BY A.amsfggid


HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: Table variables and joins

select
a.amsfggid,
a.amsdescription,
sum(b.qtysold)
from
ams a
inner join @results b
on b.amsfggid = a.amsfggid
group by
a.amsfggid, a.amsdescription

On Mar 31, 2:57 pm, hharry wrote:
> Hello All,
>
> I have this table:
>
> CREATE TABLE [dbo].[AMS](
> [AMSFGGID] [int] IDENTITY(1,1) NOT NULL,
> [AMSDESCRIPTION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
>
> GO
> SET ANSI_PADDING OFF
>
> with these values:
>
> INSERT INTO AMS (AMSDESCRIPTION)
> VALUES ('TEST DESC 1')
> INSERT INTO AMS (AMSDESCRIPTION)
> VALUES ('TEST DESC 2')
>
> I also have a script which creates a table variable:
>
> DECLARE @RESULTS Table
> (
> ROWID INT IDENTITY(1,1),
> AMSFGGID INT,
> QTYSOLD FLOAT
> )
> INSERT @RESULTS
> (
> AMSFGGID,
> QTYSOLD
> )
> VALUES
> (
> 1, 300
> )
> INSERT @RESULTS
> (
> AMSFGGID,
> QTYSOLD
> )
> VALUES
> (
> 1, 700
> )
>
> I'm trying to get construct a join which will sum the quantities sold
> for each AMS record, something like:
>
> SELECT
> A.AMSFGGID, S.TotalSales
> FROM
> AMS A
> INNER JOIN
> (SELECT
> AMSFGGID, SUM(QTYSOLD) as TotalSales
> FROM
> @RESULTS
> GROUP BY
> AMSFGGID) S
> ON
> A.AMSFGGID = S.AMSFGGID
>
> Pointers appreciated!

Re: Table variables and joins

>> Pointers appreciated! <<

FWIW, you should soon be able to build a table constant using a CTE
and a VALUES() construct, Might want to make a note in the code for
the guy who will be maintaining it later.