?
Solved

joining two queries

Posted on 2005-05-02
18
Medium Priority
?
1,406 Views
Last Modified: 2012-05-05
Hi All
I have to queries

select name,id from person

select id,sum(sales) from sales

How do I join these to queries?
criteria
You can only use select statement (can use joins). I need to list all the persons name and sum of sales
Cheers
0
Comment
Question by:tshiyam
18 Comments
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13908859
Is this homework?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13908861
Try this:

SELECT A.Name, SUM(B.Sales)
FROM Person A INNER JOIN Sales B ON A.ID = B.ID
GROUP BY A.Name
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13908868
I hope this is not homework as I already provided the answer.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:tshiyam
ID: 13908911

It doesn't list all the names
0
 

Author Comment

by:tshiyam
ID: 13908932
and I wanted to join to different queries not the tables
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13908939
Please answer Kevin3NF's question first before we can help you with this.
0
 

Author Comment

by:tshiyam
ID: 13908946
Dont worry. Ill "accept" the answer if it fulfills my question. Trust me.
cheers
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13909003
It's not about whether you accept the answer or not.  It is about following the Membership Guidelines:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi56

Be honest about your purpose

 
The Experts won't do your homework for you; they can be suspended for it, and so will you. If you have homework, and don't understand part of it, be honest about it. The Experts will be more than happy to teach you. But asking them to do your homework is the equivalent of asking someone to take an exam for you, and will be met with similar disfavor.

You also need to be as clear as possible about what it is you're trying to accomplish; don't try to hide your goals behind a vague question, if only because the Experts won't be able to give you good answers.
 

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi105

Homework

 
Experts are not allowed to do your homework for you. Doing homework for you is what the Membership Guidelines calls "academic dishonesty." In other words, it's bad enough that a member asks someone to do his or her homework; it's far worse that an Expert enables what is tantamount to cheating.

What you CAN do is guide an asker through the process of figuring out their homework. To put it at its most ridiculously sublime: You can teach a person to count, but don't tell him that 2 + 2 = 4.
 
0
 

Author Comment

by:tshiyam
ID: 13909040
I am sorry. I am not a student. And this is for my own purpose. It is not a homework.
cheers
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13909163
Ok.  Now that that is clear, try this:

>> It doesn't list all the names <<

SELECT A.Name, SUM(B.Sales)
FROM Person A LEFT OUTER JOIN Sales B ON A.ID = B.ID
GROUP BY A.Name

>> and I wanted to join to different queries not the tables <<

What do you mean by this?  Please give an example.
0
 

Author Comment

by:tshiyam
ID: 13909236
I have two tables.
person(pid,name)
sales(pid,sales,date)

I need to list all the names (doesn't matter he/she did any sale or not) and total amount of sales done in the previous month and count of sales done in the current month. I can do these three in different queries. But I am unable to join these queries. I dont what to create any extra tables or views. I only wanted use select statement.
cheers
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13909315
SELECT A.Name, DATEPART(M, B.Date), SUM(Sales)
FROM Person A LEFT OUTER JOIN Sales B
    ON A.PID = B.PID AND
         B.Date >= DATEADD(M, -1, DATEADD(D, -(DAY(GETDATE()) + 1), GETDATE()))
GROUP BY A.Name, DATEPART(M, B.Date)
0
 

Author Comment

by:tshiyam
ID: 13909446
sorry. It is not working. Could you please tell me, is it possible to join two queries. for an example

in one query i get all the persons name
in the other one i get pid and the sum of previous month

how do I join these two
0
 
LVL 28

Assisted Solution

by:rafrancisco
rafrancisco earned 220 total points
ID: 13909649
Yes, it is possible to join 2 queries and you do it using the JOIN clause of the SELECT statement.  Since you want all person's names in the output, you will be using the LEFT OUTER JOIN clause.  To join the 2 tables, given that the column that joins them is the PID column:

SELECT A.Name, SUM(Sales)
FROM Person A LEFT OUTER JOIN Sales B
    ON A.PID = B.PID
GROUP BY A.Name

If you have a query that returns just the PID and sum of the previous month's sale, this is how it will look like:

SELECT A.Name, SUM(Sales)
FROM Person A LEFT OUTER JOIN (SELECT PID, SUM(Sales) FROM Sales GROUP BY PID) B
    ON A.PID = B.PID
GROUP BY A.Name

Simply replace this part:

SELECT PID, SUM(Sales) FROM Sales GROUP BY PID

with your query.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 280 total points
ID: 13910368
I'm assuming that by "current month" and "prior month" you meant calendar months based on current date.  For example, since today is May 2, the months would be April (prior) and May (current).

The code below assumes "SMALLDATETIME".  If it's a full DATETIME, change all "MINUTE, -1" to "MS, -3":


SELECT p.pid, p.name,
      SUM(CASE WHEN s.date BETWEEN CONVERT(CHAR(6), GETDATE(), 112) + '01' AND
          DATEADD(MINUTE, -1, DATEADD(MONTH, 1, CONVERT(CHAR(6), GETDATE(), 112) + '01'))
               THEN sales ELSE 0 END) AS [Current Month],
      SUM(CASE WHEN s.date BETWEEN CONVERT(CHAR(6), DATEADD(MONTH, -1, GETDATE()), 112) + '01' AND
          DATEADD(MINUTE, -1, CONVERT(CHAR(6), GETDATE(), 112) + '01')
               THEN sales ELSE 0 END) AS [Previous Month]
FROM person p
LEFT OUTER JOIN sales s ON p.pid = s.pid
WHERE s.date BETWEEN CONVERT(CHAR(6), DATEADD(MONTH, -1, GETDATE()), 112) + '01' AND
      DATEADD(MINUTE, -1, DATEADD(MONTH, 1, CONVERT(CHAR(6), GETDATE(), 112) + '01'))
0
 

Author Comment

by:tshiyam
ID: 13914241
ScottPletcher
I thing i need a groub by clause. I have added group by clause. But the problem in your coding is, it doesn't display all the names. It only displays the name who did sales either in the current month or previous month.
cheers
0
 

Author Comment

by:tshiyam
ID: 13914449
it is working now. I just simply deleted the where clause after the join and put group by clause. Thanks for the help guys. I use my own method to calculate current and previous month.

eg
date <= DateAdd(m,-0,getDate()) and date > DateAdd(m,-1,getDate())

cheers
0
 

Author Comment

by:tshiyam
ID: 13914535
eg was wrong sorry
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

840 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