[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

SQL Join to join 2 select statments

Hello all,
sorry for my query, it's a long one

basically it consists of 2 select statements on 2 tables, each select statement gets the top 10 and self join it to the rest 90 as others.

now I need to join the 2 statements together full join, as there's of course different top 10s in each of them.

here's my query but it gives me weird results !! only results from the second statement and 3 NULL columns !!

please tell me if sample data is needed.

SELECT a.[Service Code]
, a.[Department Total]
, a.[Percentage]
from
(SELECT [Service Code]
, [Department Total]
, [Percentage]
from(
SELECT TOP 10
[Service Code]
,Sum([Total Charges]) AS [Department Total]
,Sum([Total Charges])/(Select SUM([Total Charges]) from [ExctemedDB].[dbo].[XACTIMED-COMM] Where [Fac Name] like '%Michaels%') AS [Percentage]
from dbo.[XACTIMED-COMM]
Where [Fac Name] like '%Michaels%'
GROUP BY
[Service Code]
ORDER BY
[Department Total] DESC
) AS Q1
UNION ALL
SELECT
'Other' AS [Service Code]
,Sum([Total Charges]) AS [Department Total]
,Sum([Total Charges])/(Select SUM([Total Charges]) from [ExctemedDB].[dbo].[XACTIMED-COMM]) AS [Percentage]
from dbo.[XACTIMED-COMM]
Where [Fac Name] like '%Michaels%'
and
[Service Code] NOT IN (SELECT TOP 10
[Service Code]
from dbo.[XACTIMED-COMM]
Where [Fac Name] like '%Michaels%'
GROUP BY
[Service Code]
ORDER BY
Sum([Total Charges]) DESC))a


full join



(SELECT [Service Code]
, [Department Total]
, [Percentage]
from(
SELECT TOP 10
[Service Code]
,Sum([Total Charges]) AS [Department Total]
,Sum([Total Charges])/(Select SUM([Total Charges]) from [ExctemedDB].[dbo].[XACTIMED-CARE] Where [Fac Name] like '%Michaels%') AS [Percentage]
from dbo.[XACTIMED-CARE]
Where [Fac Name] like '%Michaels%'
GROUP BY
[Service Code]
ORDER BY
[Department Total] DESC
) AS Q2
UNION ALL
SELECT
'Other' AS [Service Code]
,Sum([Total Charges]) AS [Department Total]
,Sum([Total Charges])/(Select SUM([Total Charges]) from [ExctemedDB].[dbo].[XACTIMED-CARE]) AS [Percentage]
from dbo.[XACTIMED-CARE]
Where [Fac Name] like '%Michaels%'
and
[Service Code] NOT IN (SELECT TOP 10
[Service Code]
from dbo.[XACTIMED-CARE]
Where [Fac Name] like '%Michaels%'
GROUP BY
[Service Code]
ORDER BY
Sum([Total Charges]) DESC))d

on d.[Service Code] = a.[Service Code]

Open in new window


can somebody help me please.

Thanks results
0
Jason Yousef, MS
Asked:
Jason Yousef, MS
  • 6
  • 5
  • 2
1 Solution
 
vdr1620Commented:
can you provide sample data from the tables along with table structure ??
0
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
Hi vdr1620:

thanks for your reply.
here you go attached.

sample-data-x.txt
0
 
ajisasaggiCommented:
Could you please state your requirement? Do the individual statements produce expected results?

If it is combining the results of the 2 separate queries, then you should do a UNION of the statements instead of JOIN.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
ajisasaggi:  thanks for stepping in, union gives me duplicates, doesn't sum all the same service departments.

can't figure it out !
0
 
ajisasaggiCommented:
So do you want to sum the totals, [Service Code] wise, from both statements?
What about 'Other'? Do you want a single row with combined totals?
0
 
vdr1620Commented:
Thanks for data..but go to go.. will work on it as soon as i come back
0
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
ajisasaggi:  yes, need to sum all the departments that match and other too.

as it's a combined 2 statements together
0
 
ajisasaggiCommented:
Please try the attached query.
It can be cleaned up quite a bit (percentage values from inner queries can be removed).

query.sql
0
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
ajisasaggi:  thank you, but it gave me error, attached.

I'm using sql server 2008 dev

error.gif
0
 
ajisasaggiCommented:
Please try the attached query.
The idea is to combine the results of the 4 inner SELECT statements and apply grouping on the resultset.
query.sql
0
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
ajisasaggi: thanks for your help, but what version of sql you're using? how come it works for you and i keep getting errors !

I got punch of Incorrect syntax near ')'
0
 
ajisasaggiCommented:
My bad, I didn't validate the syntax.

Please try with this. I have modified the query to return the top 10 departments that have a combined higher total and an additional row with the accumulated total for all other departments.
SELECT [Service Code], [Department Total]
FROM
(
    SELECT TOP 10 [Service Code], SUM([Total Charges]) AS [Department Total]
        , SUM([Total Charges])/(Select SUM([Total Charges]) from [ExctemedDB].[dbo].[XACTIMED-COMM] Where [Fac Name] like '%Michaels%') + (Select SUM([Total Charges]) from [ExctemedDB].[dbo].[XACTIMED-CARE] Where [Fac Name] like '%Michaels%') AS [Percentage]
    FROM
    (
        SELECT [Service Code], [Total Charges]
        from dbo.[XACTIMED-COMM]
        Where [Fac Name] like '%Michaels%'
        union all
        SELECT [Service Code], [Total Charges]
        from dbo.[XACTIMED-CARE]
        Where [Fac Name] like '%Michaels%'
    ) AS ALL_DEPT
    GROUP BY [Service Code]
    ORDER BY [Department Total] DESC
) AS TOP_TEN

UNION ALL

SELECT 'Other' [Service Code], SUM([Total Charges]) AS [Department Total]
    , SUM([Total Charges])/(Select SUM([Total Charges]) from [ExctemedDB].[dbo].[XACTIMED-COMM] Where [Fac Name] like '%Michaels%') + (Select SUM([Total Charges]) from [ExctemedDB].[dbo].[XACTIMED-CARE] Where [Fac Name] like '%Michaels%') AS [Percentage]
FROM
(
    SELECT [Service Code], [Total Charges]
    from dbo.[XACTIMED-COMM]
    Where [Fac Name] like '%Michaels%'
    union all
    SELECT [Service Code], [Total Charges]
    from dbo.[XACTIMED-CARE]
    Where [Fac Name] like '%Michaels%'
) AS ALL_DEPT
WHERE [Service Code] NOT IN (
    SELECT TOP 10 [Service Code]
    FROM
    (
        SELECT [Service Code], [Total Charges]
        from dbo.[XACTIMED-COMM]
        Where [Fac Name] like '%Michaels%'
        union all
        SELECT [Service Code], [Total Charges]
        from dbo.[XACTIMED-CARE]
        Where [Fac Name] like '%Michaels%'
    ) AS ALL_DEPT
    GROUP BY [Service Code]
    ORDER BY [Department Total] DESC
)

Open in new window

0
 
Jason Yousef, MSSr. BI  DeveloperAuthor Commented:
Thanks for trying, but still a lot of syntax errors, I did it in a different way though.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now