Need help with UNION statement

Hello,
I'm trying to join results from two tables and still don't understand why
the following statement doesn't work:
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
FROM businesspartners bp INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp2 ON
bp.rating = bp2.rating
WHERE (bp.rating <> '') AND (bp.rating IS NOT
NULL)
ORDER BY bp2.Cnt DESC)
UNION
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
FROM businesspartners bp3 INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp4 ON
bp3.rating = bp4.rating
WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
NULL)
ORDER BY bp4.Cnt DESC)
I tried each table and I get correct results, only when I tried to "union"
them it's giving me "Incorrect syntax near the keyword 'UNION'"
Give an alias to each derived table.
SELECT rating
FROM
(
SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
FROM businesspartners bp INNER JOIN
(
SELECT rating, COUNT(*) AS Cnt
FROM businesspartners
GROUP BY rating
) bp2
ON bp.rating = bp2.rating
WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
ORDER BY bp2.Cnt DESC
) as t1 <--
UNION
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
FROM businesspartners bp3 INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp4 ON
bp3.rating = bp4.rating
WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
NULL)
ORDER BY bp4.Cnt DESC) as t2 <--
AMB
"Vlado Jasovic (excelleinc.com)" wrote:

> Hello,
> I'm trying to join results from two tables and still don't understand why
> the following statement doesn't work:
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> FROM businesspartners bp INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp2 ON
> bp.rating = bp2.rating
> WHERE (bp.rating <> '') AND (bp.rating IS NOT
> NULL)
> ORDER BY bp2.Cnt DESC)
> UNION
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> FROM businesspartners bp3 INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp4 ON
> bp3.rating = bp4.rating
> WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> NULL)
> ORDER BY bp4.Cnt DESC)
> I tried each table and I get correct results, only when I tried to "union"
> them it's giving me "Incorrect syntax near the keyword 'UNION'"
>
>
|||Also the whole query can only have one ORDER BY clause ... at the end of all
UNIONed sleect statements.
Also one note, you may or may not know the difference between "UNION" and
"UNION ALL" ... often people want "UNION ALL" and don't know it.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Give an alias to each derived table.
> SELECT rating
> FROM
> (
> SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> FROM businesspartners bp INNER JOIN
> (
> SELECT rating, COUNT(*) AS Cnt
> FROM businesspartners
> GROUP BY rating
> ) bp2
> ON bp.rating = bp2.rating
> WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
> ORDER BY bp2.Cnt DESC
> ) as t1 <--
> UNION
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> FROM businesspartners bp3 INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp4 ON
> bp3.rating = bp4.rating
> WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> NULL)
> ORDER BY bp4.Cnt DESC) as t2 <--
>
> AMB
>
> "Vlado Jasovic (excelleinc.com)" wrote:
|||KH,
I think he is using the "order by" to asure that each "select top ..." will
be consistent.
AMB
"KH" wrote:
[vbcol=seagreen]
> Also the whole query can only have one ORDER BY clause ... at the end of all
> UNIONed sleect statements.
> Also one note, you may or may not know the difference between "UNION" and
> "UNION ALL" ... often people want "UNION ALL" and don't know it.
>
> "Alejandro Mesa" wrote:
|||Thank you very much for quick and great answer.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D75847A7-61FC-4A41-BD91-8F88C6FFC1B6@.microsoft.com...[vbcol=seagreen]
> KH,
> I think he is using the "order by" to asure that each "select top ..."
> will
> be consistent.
>
> AMB
> "KH" wrote:
|||Ach, indeed... missed that!
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> KH,
> I think he is using the "order by" to asure that each "select top ..." will
> be consistent.
>
> AMB
> "KH" wrote:

Need help with UNION statement

Hello,
I'm trying to join results from two tables and still don't understand why
the following statement doesn't work:
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
FROM businesspartners bp INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp2 ON
bp.rating = bp2.rating
WHERE (bp.rating <> '') AND (bp.rating IS NOT
NULL)
ORDER BY bp2.Cnt DESC)
UNION
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
FROM businesspartners bp3 INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp4 ON
bp3.rating = bp4.rating
WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
NULL)
ORDER BY bp4.Cnt DESC)
I tried each table and I get correct results, only when I tried to "union"
them it's giving me "Incorrect syntax near the keyword 'UNION'"Give an alias to each derived table.
SELECT rating
FROM
(
SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
FROM businesspartners bp INNER JOIN
(
SELECT rating, COUNT(*) AS Cnt
FROM businesspartners
GROUP BY rating
) bp2
ON bp.rating = bp2.rating
WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
ORDER BY bp2.Cnt DESC
) as t1 <--
UNION
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
FROM businesspartners bp3 INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp4 ON
bp3.rating = bp4.rating
WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
NULL)
ORDER BY bp4.Cnt DESC) as t2 <--
AMB
"Vlado Jasovic (excelleinc.com)" wrote:

> Hello,
> I'm trying to join results from two tables and still don't understand why
> the following statement doesn't work:
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> FROM businesspartners bp INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp2 O
N
> bp.rating = bp2.rating
> WHERE (bp.rating <> '') AND (bp.rating IS NOT
> NULL)
> ORDER BY bp2.Cnt DESC)
> UNION
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> FROM businesspartners bp3 INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp4 O
N
> bp3.rating = bp4.rating
> WHERE (bp3.rating <> '') AND (bp3.rating IS NO
T
> NULL)
> ORDER BY bp4.Cnt DESC)
> I tried each table and I get correct results, only when I tried to "union"
> them it's giving me "Incorrect syntax near the keyword 'UNION'"
>
>|||Also the whole query can only have one ORDER BY clause ... at the end of all
UNIONed sleect statements.
Also one note, you may or may not know the difference between "UNION" and
"UNION ALL" ... often people want "UNION ALL" and don't know it.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Give an alias to each derived table.
> SELECT rating
> FROM
> (
> SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> FROM businesspartners bp INNER JOIN
> (
> SELECT rating, COUNT(*) AS Cnt
> FROM businesspartners
> GROUP BY rating
> ) bp2
> ON bp.rating = bp2.rating
> WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
> ORDER BY bp2.Cnt DESC
> ) as t1 <--
> UNION
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> FROM businesspartners bp3 INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp4 O
N
> bp3.rating = bp4.rating
> WHERE (bp3.rating <> '') AND (bp3.rating IS NO
T
> NULL)
> ORDER BY bp4.Cnt DESC) as t2 <--
>
> AMB
>
> "Vlado Jasovic (excelleinc.com)" wrote:
>|||KH,
I think he is using the "order by" to asure that each "select top ..." will
be consistent.
AMB
"KH" wrote:
[vbcol=seagreen]
> Also the whole query can only have one ORDER BY clause ... at the end of a
ll
> UNIONed sleect statements.
> Also one note, you may or may not know the difference between "UNION" and
> "UNION ALL" ... often people want "UNION ALL" and don't know it.
>
> "Alejandro Mesa" wrote:
>|||Thank you very much for quick and great answer.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D75847A7-61FC-4A41-BD91-8F88C6FFC1B6@.microsoft.com...[vbcol=seagreen]
> KH,
> I think he is using the "order by" to asure that each "select top ..."
> will
> be consistent.
>
> AMB
> "KH" wrote:
>|||Ach, indeed... missed that!
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> KH,
> I think he is using the "order by" to asure that each "select top ..." wil
l
> be consistent.
>
> AMB
> "KH" wrote:
>

Need help with UNION statement

Hello,
I'm trying to join results from two tables and still don't understand why
the following statement doesn't work:
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
FROM businesspartners bp INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp2 ON
bp.rating = bp2.rating
WHERE (bp.rating <> '') AND (bp.rating IS NOT
NULL)
ORDER BY bp2.Cnt DESC)
UNION
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
FROM businesspartners bp3 INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp4 ON
bp3.rating = bp4.rating
WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
NULL)
ORDER BY bp4.Cnt DESC)
I tried each table and I get correct results, only when I tried to "union"
them it's giving me "Incorrect syntax near the keyword 'UNION'"Give an alias to each derived table.
SELECT rating
FROM
(
SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
FROM businesspartners bp INNER JOIN
(
SELECT rating, COUNT(*) AS Cnt
FROM businesspartners
GROUP BY rating
) bp2
ON bp.rating = bp2.rating
WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
ORDER BY bp2.Cnt DESC
) as t1 <--
UNION
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
FROM businesspartners bp3 INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp4 ON
bp3.rating = bp4.rating
WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
NULL)
ORDER BY bp4.Cnt DESC) as t2 <--
AMB
"Vlado Jasovic (excelleinc.com)" wrote:

> Hello,
> I'm trying to join results from two tables and still don't understand why
> the following statement doesn't work:
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> FROM businesspartners bp INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp2 O
N
> bp.rating = bp2.rating
> WHERE (bp.rating <> '') AND (bp.rating IS NOT
> NULL)
> ORDER BY bp2.Cnt DESC)
> UNION
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> FROM businesspartners bp3 INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp4 O
N
> bp3.rating = bp4.rating
> WHERE (bp3.rating <> '') AND (bp3.rating IS NO
T
> NULL)
> ORDER BY bp4.Cnt DESC)
> I tried each table and I get correct results, only when I tried to "union"
> them it's giving me "Incorrect syntax near the keyword 'UNION'"
>
>|||Also the whole query can only have one ORDER BY clause ... at the end of all
UNIONed sleect statements.
Also one note, you may or may not know the difference between "UNION" and
"UNION ALL" ... often people want "UNION ALL" and don't know it.
"Alejandro Mesa" wrote:
> Give an alias to each derived table.
> SELECT rating
> FROM
> (
> SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> FROM businesspartners bp INNER JOIN
> (
> SELECT rating, COUNT(*) AS Cnt
> FROM businesspartners
> GROUP BY rating
> ) bp2
> ON bp.rating = bp2.rating
> WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
> ORDER BY bp2.Cnt DESC
> ) as t1 <--
> UNION
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> FROM businesspartners bp3 INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp4 O
N
> bp3.rating = bp4.rating
> WHERE (bp3.rating <> '') AND (bp3.rating IS NO
T
> NULL)
> ORDER BY bp4.Cnt DESC) as t2 <--
>
> AMB
>
> "Vlado Jasovic (excelleinc.com)" wrote:
>|||KH,
I think he is using the "order by" to asure that each "select top ..." will
be consistent.
AMB
"KH" wrote:
> Also the whole query can only have one ORDER BY clause ... at the end of a
ll
> UNIONed sleect statements.
> Also one note, you may or may not know the difference between "UNION" and
> "UNION ALL" ... often people want "UNION ALL" and don't know it.
>
> "Alejandro Mesa" wrote:
>|||Thank you very much for quick and great answer.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D75847A7-61FC-4A41-BD91-8F88C6FFC1B6@.microsoft.com...
> KH,
> I think he is using the "order by" to asure that each "select top ..."
> will
> be consistent.
>
> AMB
> "KH" wrote:
>|||Ach, indeed... missed that!
"Alejandro Mesa" wrote:
> KH,
> I think he is using the "order by" to asure that each "select top ..." wil
l
> be consistent.
>
> AMB
> "KH" wrote:
>

Need help with UNION statement

Hello,
I'm trying to join results from two tables and still don't understand why
the following statement doesn't work:
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
FROM businesspartners bp INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp2 ON
bp.rating = bp2.rating
WHERE (bp.rating <> '') AND (bp.rating IS NOT
NULL)
ORDER BY bp2.Cnt DESC)
UNION
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
FROM businesspartners bp3 INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp4 ON
bp3.rating = bp4.rating
WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
NULL)
ORDER BY bp4.Cnt DESC)
I tried each table and I get correct results, only when I tried to "union"
them it's giving me "Incorrect syntax near the keyword 'UNION'"Give an alias to each derived table.
SELECT rating
FROM
(
SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
FROM businesspartners bp INNER JOIN
(
SELECT rating, COUNT(*) AS Cnt
FROM businesspartners
GROUP BY rating
) bp2
ON bp.rating = bp2.rating
WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
ORDER BY bp2.Cnt DESC
) as t1 <--
UNION
SELECT rating
FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
FROM businesspartners bp3 INNER JOIN
(SELECT rating,
COUNT(*) AS Cnt
FROM
businesspartners
GROUP BY rating) bp4 ON
bp3.rating = bp4.rating
WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
NULL)
ORDER BY bp4.Cnt DESC) as t2 <--
AMB
"Vlado Jasovic (excelleinc.com)" wrote:
> Hello,
> I'm trying to join results from two tables and still don't understand why
> the following statement doesn't work:
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> FROM businesspartners bp INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp2 ON
> bp.rating = bp2.rating
> WHERE (bp.rating <> '') AND (bp.rating IS NOT
> NULL)
> ORDER BY bp2.Cnt DESC)
> UNION
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> FROM businesspartners bp3 INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp4 ON
> bp3.rating = bp4.rating
> WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> NULL)
> ORDER BY bp4.Cnt DESC)
> I tried each table and I get correct results, only when I tried to "union"
> them it's giving me "Incorrect syntax near the keyword 'UNION'"
>
>|||Also the whole query can only have one ORDER BY clause ... at the end of all
UNIONed sleect statements.
Also one note, you may or may not know the difference between "UNION" and
"UNION ALL" ... often people want "UNION ALL" and don't know it.
"Alejandro Mesa" wrote:
> Give an alias to each derived table.
> SELECT rating
> FROM
> (
> SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> FROM businesspartners bp INNER JOIN
> (
> SELECT rating, COUNT(*) AS Cnt
> FROM businesspartners
> GROUP BY rating
> ) bp2
> ON bp.rating = bp2.rating
> WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
> ORDER BY bp2.Cnt DESC
> ) as t1 <--
> UNION
> SELECT rating
> FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> FROM businesspartners bp3 INNER JOIN
> (SELECT rating,
> COUNT(*) AS Cnt
> FROM
> businesspartners
> GROUP BY rating) bp4 ON
> bp3.rating = bp4.rating
> WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> NULL)
> ORDER BY bp4.Cnt DESC) as t2 <--
>
> AMB
>
> "Vlado Jasovic (excelleinc.com)" wrote:
> > Hello,
> >
> > I'm trying to join results from two tables and still don't understand why
> > the following statement doesn't work:
> >
> > SELECT rating
> > FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> > FROM businesspartners bp INNER JOIN
> > (SELECT rating,
> > COUNT(*) AS Cnt
> > FROM
> > businesspartners
> > GROUP BY rating) bp2 ON
> > bp.rating = bp2.rating
> > WHERE (bp.rating <> '') AND (bp.rating IS NOT
> > NULL)
> > ORDER BY bp2.Cnt DESC)
> > UNION
> > SELECT rating
> > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> > FROM businesspartners bp3 INNER JOIN
> > (SELECT rating,
> > COUNT(*) AS Cnt
> > FROM
> > businesspartners
> > GROUP BY rating) bp4 ON
> > bp3.rating = bp4.rating
> > WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> > NULL)
> > ORDER BY bp4.Cnt DESC)
> >
> > I tried each table and I get correct results, only when I tried to "union"
> > them it's giving me "Incorrect syntax near the keyword 'UNION'"
> >
> >
> >|||KH,
I think he is using the "order by" to asure that each "select top ..." will
be consistent.
AMB
"KH" wrote:
> Also the whole query can only have one ORDER BY clause ... at the end of all
> UNIONed sleect statements.
> Also one note, you may or may not know the difference between "UNION" and
> "UNION ALL" ... often people want "UNION ALL" and don't know it.
>
> "Alejandro Mesa" wrote:
> > Give an alias to each derived table.
> >
> > SELECT rating
> > FROM
> > (
> > SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> > FROM businesspartners bp INNER JOIN
> > (
> > SELECT rating, COUNT(*) AS Cnt
> > FROM businesspartners
> > GROUP BY rating
> > ) bp2
> > ON bp.rating = bp2.rating
> > WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
> > ORDER BY bp2.Cnt DESC
> > ) as t1 <--
> >
> > UNION
> >
> > SELECT rating
> > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> > FROM businesspartners bp3 INNER JOIN
> > (SELECT rating,
> > COUNT(*) AS Cnt
> > FROM
> > businesspartners
> > GROUP BY rating) bp4 ON
> > bp3.rating = bp4.rating
> > WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> > NULL)
> > ORDER BY bp4.Cnt DESC) as t2 <--
> >
> >
> > AMB
> >
> >
> > "Vlado Jasovic (excelleinc.com)" wrote:
> >
> > > Hello,
> > >
> > > I'm trying to join results from two tables and still don't understand why
> > > the following statement doesn't work:
> > >
> > > SELECT rating
> > > FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> > > FROM businesspartners bp INNER JOIN
> > > (SELECT rating,
> > > COUNT(*) AS Cnt
> > > FROM
> > > businesspartners
> > > GROUP BY rating) bp2 ON
> > > bp.rating = bp2.rating
> > > WHERE (bp.rating <> '') AND (bp.rating IS NOT
> > > NULL)
> > > ORDER BY bp2.Cnt DESC)
> > > UNION
> > > SELECT rating
> > > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> > > FROM businesspartners bp3 INNER JOIN
> > > (SELECT rating,
> > > COUNT(*) AS Cnt
> > > FROM
> > > businesspartners
> > > GROUP BY rating) bp4 ON
> > > bp3.rating = bp4.rating
> > > WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> > > NULL)
> > > ORDER BY bp4.Cnt DESC)
> > >
> > > I tried each table and I get correct results, only when I tried to "union"
> > > them it's giving me "Incorrect syntax near the keyword 'UNION'"
> > >
> > >
> > >|||Thank you very much for quick and great answer.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D75847A7-61FC-4A41-BD91-8F88C6FFC1B6@.microsoft.com...
> KH,
> I think he is using the "order by" to asure that each "select top ..."
> will
> be consistent.
>
> AMB
> "KH" wrote:
>> Also the whole query can only have one ORDER BY clause ... at the end of
>> all
>> UNIONed sleect statements.
>> Also one note, you may or may not know the difference between "UNION" and
>> "UNION ALL" ... often people want "UNION ALL" and don't know it.
>>
>> "Alejandro Mesa" wrote:
>> > Give an alias to each derived table.
>> >
>> > SELECT rating
>> > FROM
>> > (
>> > SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
>> > FROM businesspartners bp INNER JOIN
>> > (
>> > SELECT rating, COUNT(*) AS Cnt
>> > FROM businesspartners
>> > GROUP BY rating
>> > ) bp2
>> > ON bp.rating = bp2.rating
>> > WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
>> > ORDER BY bp2.Cnt DESC
>> > ) as t1 <--
>> >
>> > UNION
>> >
>> > SELECT rating
>> > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
>> > FROM businesspartners bp3 INNER JOIN
>> > (SELECT rating,
>> > COUNT(*) AS Cnt
>> > FROM
>> > businesspartners
>> > GROUP BY rating)
>> > bp4 ON
>> > bp3.rating = bp4.rating
>> > WHERE (bp3.rating <> '') AND (bp3.rating IS
>> > NOT
>> > NULL)
>> > ORDER BY bp4.Cnt DESC) as t2 <--
>> >
>> >
>> > AMB
>> >
>> >
>> > "Vlado Jasovic (excelleinc.com)" wrote:
>> >
>> > > Hello,
>> > >
>> > > I'm trying to join results from two tables and still don't understand
>> > > why
>> > > the following statement doesn't work:
>> > >
>> > > SELECT rating
>> > > FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
>> > > FROM businesspartners bp INNER JOIN
>> > > (SELECT rating,
>> > > COUNT(*) AS Cnt
>> > > FROM
>> > > businesspartners
>> > > GROUP BY rating)
>> > > bp2 ON
>> > > bp.rating = bp2.rating
>> > > WHERE (bp.rating <> '') AND (bp.rating IS
>> > > NOT
>> > > NULL)
>> > > ORDER BY bp2.Cnt DESC)
>> > > UNION
>> > > SELECT rating
>> > > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
>> > > FROM businesspartners bp3 INNER JOIN
>> > > (SELECT rating,
>> > > COUNT(*) AS Cnt
>> > > FROM
>> > > businesspartners
>> > > GROUP BY rating)
>> > > bp4 ON
>> > > bp3.rating = bp4.rating
>> > > WHERE (bp3.rating <> '') AND (bp3.rating
>> > > IS NOT
>> > > NULL)
>> > > ORDER BY bp4.Cnt DESC)
>> > >
>> > > I tried each table and I get correct results, only when I tried to
>> > > "union"
>> > > them it's giving me "Incorrect syntax near the keyword 'UNION'"
>> > >
>> > >
>> > >|||Ach, indeed... missed that!
"Alejandro Mesa" wrote:
> KH,
> I think he is using the "order by" to asure that each "select top ..." will
> be consistent.
>
> AMB
> "KH" wrote:
> > Also the whole query can only have one ORDER BY clause ... at the end of all
> > UNIONed sleect statements.
> >
> > Also one note, you may or may not know the difference between "UNION" and
> > "UNION ALL" ... often people want "UNION ALL" and don't know it.
> >
> >
> > "Alejandro Mesa" wrote:
> >
> > > Give an alias to each derived table.
> > >
> > > SELECT rating
> > > FROM
> > > (
> > > SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> > > FROM businesspartners bp INNER JOIN
> > > (
> > > SELECT rating, COUNT(*) AS Cnt
> > > FROM businesspartners
> > > GROUP BY rating
> > > ) bp2
> > > ON bp.rating = bp2.rating
> > > WHERE (bp.rating <> '') AND (bp.rating IS NOT NULL)
> > > ORDER BY bp2.Cnt DESC
> > > ) as t1 <--
> > >
> > > UNION
> > >
> > > SELECT rating
> > > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> > > FROM businesspartners bp3 INNER JOIN
> > > (SELECT rating,
> > > COUNT(*) AS Cnt
> > > FROM
> > > businesspartners
> > > GROUP BY rating) bp4 ON
> > > bp3.rating = bp4.rating
> > > WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> > > NULL)
> > > ORDER BY bp4.Cnt DESC) as t2 <--
> > >
> > >
> > > AMB
> > >
> > >
> > > "Vlado Jasovic (excelleinc.com)" wrote:
> > >
> > > > Hello,
> > > >
> > > > I'm trying to join results from two tables and still don't understand why
> > > > the following statement doesn't work:
> > > >
> > > > SELECT rating
> > > > FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt
> > > > FROM businesspartners bp INNER JOIN
> > > > (SELECT rating,
> > > > COUNT(*) AS Cnt
> > > > FROM
> > > > businesspartners
> > > > GROUP BY rating) bp2 ON
> > > > bp.rating = bp2.rating
> > > > WHERE (bp.rating <> '') AND (bp.rating IS NOT
> > > > NULL)
> > > > ORDER BY bp2.Cnt DESC)
> > > > UNION
> > > > SELECT rating
> > > > FROM (SELECT DISTINCT TOP 10 bp3.category AS rating, bp4.Cnt
> > > > FROM businesspartners bp3 INNER JOIN
> > > > (SELECT rating,
> > > > COUNT(*) AS Cnt
> > > > FROM
> > > > businesspartners
> > > > GROUP BY rating) bp4 ON
> > > > bp3.rating = bp4.rating
> > > > WHERE (bp3.rating <> '') AND (bp3.rating IS NOT
> > > > NULL)
> > > > ORDER BY bp4.Cnt DESC)
> > > >
> > > > I tried each table and I get correct results, only when I tried to "union"
> > > > them it's giving me "Incorrect syntax near the keyword 'UNION'"
> > > >
> > > >
> > > >

Need help with UNION and SUM

Hi!

I am trying to join to different queries into one table ( I accomplished this)

Next I need to ADD or SUM the results of 2 rows to form a single row.

As you can see in the query below, I run 2 separate queries and use ' ' as a place holder for the UNION to work. I get duplicate rows, one with a value and the other with a '0'. I want to have a single row.

Any help is greatly appreciated!

(SELECT
v_gs_supportedpackages.ProdID0 as 'Product Name',
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site',
Count(ProdID0) as '# copies installed',
'' as '# legitimate copies installed'

FROM
v_R_System SYS,
v_GS_Workstation_Status HWSCAN,
v_gs_SupportedPackages
inner join
v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID

WHERE
SYS.ResourceId = HWSCAN.ResourceId
AND
SYS.ResourceId = v_gs_SupportedPackages.ResourceId
AND
v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)

GROUP BY

v_gs_supportedpackages.ProdID0,
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 )

UNION

(SELECT
v_gs_supportedpackages.ProdID0 as 'Product Name',
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site',
'' as '# copies installed',
Count(ProdID0) as '# legitimate copies installed'

FROM
v_R_System SYS,
v_GS_Workstation_Status HWSCAN,
v_gs_SupportedPackages
inner join
v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID

WHERE
SYS.ResourceId = HWSCAN.ResourceId
AND
SYS.ResourceId = v_gs_SupportedPackages.ResourceId
AND
v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
AND
DateDiff(Day,HWSCAN.LastHWScan,GetDate()) <= '20'

GROUP BY

v_gs_supportedpackages.ProdID0,
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 )Change your null string place holder to a zero and use a table variable to return only 1 row for each instance of ColA and ColB. For example

DECLARE @.myTable table
(
ColA varchar(50), CoB varchar(50), ColC int, ColD int
)

INSERT INTO @.myTable
(ColA, ColB, ColC, ColD)

SELECT ColA, ColB, SUM(ColC), 0
FROM TableA

UNION ALL

SELECT ColA, ColB, 0, Sum(ColD)
FROM TableB
GROUP BY ColA, ColB

ORDER BY 1, 2

SELECT ColA, ColB, SUM(ColC) AS ColC, SUM(ColD) AS ColD
FROM @.myTable
GROUP BY ColA, ColB
ORDER BY 1, 2
GO|||First, fix your queries to eliminate joining tables in your WHERE clauses.
Second, you don't (and shouldn't) enclose numeric values in quotes.
Third, you don't need UNION here. You need a full outer join:

SELECT COPIESINSTALLED.ProdID0 as 'Product Name', COPIESINSTALLED.SMS_Installed_Sites0 as 'Site', COPIESINSTALLED.TOTAL as '# copies installed', LEGITCOPIESINSTALLED.TOTAL as '# legitimate copies installed'
FROM
(SELECT v_gs_supportedpackages.ProdID0, v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 , Count(ProdID0) TOTAL
FROM v_R_System SYS,
INNER JOIN v_GS_Workstation_Status HWSCAN ON SYS.ResourceId = HWSCAN.ResourceId
INNER JOIN v_gs_SupportedPackages ON SYS.ResourceId = v_gs_SupportedPackages.ResourceId
INNER JOIN v_RA_System_SMSInstalledSites ON v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
GROUP BY v_gs_supportedpackages.ProdID0, v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 ) COPIESINSTALLED
FULL OUTER JOIN
(SELECT v_gs_supportedpackages.ProdID0, v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 , Count(ProdID0) TOTAL
FROM v_R_System SYS,
INNER JOIN v_GS_Workstation_Status HWSCAN ON SYS.ResourceId = HWSCAN.ResourceId
INNER JOIN v_gs_SupportedPackages ON SYS.ResourceId = v_gs_SupportedPackages.ResourceId
INNER JOIN v_RA_System_SMSInstalledSites ON v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
AND DateDiff(Day,HWSCAN.LastHWScan,GetDate()) <= 20
GROUP BY v_gs_supportedpackages.ProdID0, v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 ) LEGITCOPIESINSTALLED
ON COPIESINSTALLED.ProdID0 = LEGITCOPIESINSTALLED.ProdID0
AND COPIESINSTALLED.SMS_Installed_Sites0 = LEGITCOPIESINSTALLED.SMS_Installed_Sites0

Finally, you could probably do the whole thing in a single SELECT with a CASE statement in the result set, but I'm not clear enough on your task to create the SQL for you.

...and one other thing. What is "substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)" supposed to be doing for you?|||Many thanks to you. I knew you guys would knock this out.

Thanks again.

Semper Fi,

JP|||Blindman,

Thanks again for your help. In regards to:

Finally, you could probably do the whole thing in a single SELECT with a CASE statement in the result set, but I'm not clear enough on your task to create the SQL for you.

...and one other thing. What is "substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)" supposed to be doing for you?
__________________

The substring was the only way I could pump in a variable with commas. On our web reports this value is dynamic and due to the naming conventions of our admins, some put commas in the title to separate the different versions of a package.

If you know a better way to capture commas and not have SQL try to evaluate it as an expression, please let me know!

Many thanks again,

JP