Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Join to join 2 select statments

Posted on 2010-08-17
13
Medium Priority
?
491 Views
Last Modified: 2012-05-10
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
Comment
Question by:Jason Yousef, MS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 33457166
can you provide sample data from the tables along with table structure ??
0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33457283
Hi vdr1620:

thanks for your reply.
here you go attached.

sample-data-x.txt
0
 
LVL 2

Expert Comment

by:ajisasaggi
ID: 33457285
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33457309
ajisasaggi:  thanks for stepping in, union gives me duplicates, doesn't sum all the same service departments.

can't figure it out !
0
 
LVL 2

Expert Comment

by:ajisasaggi
ID: 33457463
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33457511
Thanks for data..but go to go.. will work on it as soon as i come back
0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33457542
ajisasaggi:  yes, need to sum all the departments that match and other too.

as it's a combined 2 statements together
0
 
LVL 2

Expert Comment

by:ajisasaggi
ID: 33457801
Please try the attached query.
It can be cleaned up quite a bit (percentage values from inner queries can be removed).

query.sql
0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33458076
ajisasaggi:  thank you, but it gave me error, attached.

I'm using sql server 2008 dev

error.gif
0
 
LVL 2

Expert Comment

by:ajisasaggi
ID: 33458457
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
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 33458548
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
 
LVL 2

Accepted Solution

by:
ajisasaggi earned 1000 total points
ID: 33458862
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
 
LVL 21

Author Closing Comment

by:Jason Yousef, MS
ID: 33559261
Thanks for trying, but still a lot of syntax errors, I did it in a different way though.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question