Basic query help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Basic query help!

Author

Topic

inou
Starting Member

9 Posts

Posted - 2012-06-14 : 16:55:23

Please help anyone. I'm learning SQL here. I have 2 tables:
Tbl1:
Fname Lname MemberID
Smith M 1
David N 2

Tbl2:
MemberID RequestSubmited
1 Y
2 Y
1 Y
1 Y
2 Y
1 Y

How can I have the result below?
Fname Lname MemberID TotalSubmitted
Smith M 1 4
David N 2 2

Thanks every one!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-14 : 17:04:20

Hint: use a join, sum aggregate function and a group by.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

inou
Starting Member

9 Posts

Posted - 2012-06-14 : 17:09:40

quote:
Originally posted by tkizer

Hint: use a join, sum aggregate function and a group by.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

could you give me the query? I know it simple but I'm pretty new and learning. I'm having a project related to this. So help is much appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-14 : 17:14:03

Please show us that you have tried. While we'll help with homework, we aren't going to do it for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

inou
Starting Member

9 Posts

Posted - 2012-06-14 : 17:17:38

quote:
Originally posted by tkizer

Please show us that you have tried. While we'll help with homework, we aren't going to do it for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

thanks but this is work related, not for class. I've been trying everything for past 7 hours, I got only 1 more left to finish this. Please help me out. Much appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-14 : 17:18:34

Show us what you have tried.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

inou
Starting Member

9 Posts

Posted - 2012-06-14 : 17:25:42

here what I tried:

SELECT a.Fname, a.Lname, a.MemberID, COUNT(b.RequestSubmited) TotalSubmited
FROM Tbl1 a, Tbl2 b
WHERE a.MemberID = b.MemberID
GROUP BY a.MemeberID
ORDER BY a.Fname

Got error:
Column 'Fname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-14 : 17:34:07

Try this:

SELECT Fname, Lname, MemberID, COUNT(RequestSubmited) TotalSubmited
FROM (
SELECT a.Fname, a.Lname, a.MemberID
FROM Tbl1 a, Tbl2 b
WHERE a.MemberID = b.MemberID) t
GROUP BY Fname, Lname, MemberID
ORDER BY Fname

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

inou
Starting Member

9 Posts

Posted - 2012-06-14 : 17:41:32

quote:
Originally posted by tkizer

Try this:

SELECT Fname, Lname, MemberID, COUNT(RequestSubmited) TotalSubmited
FROM (
SELECT a.Fname, a.Lname, a.MemberID
FROM Tbl1 a, Tbl2 b
WHERE a.MemberID = b.MemberID) t
GROUP BY Fname, Lname, MemberID
ORDER BY Fname

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thanks but got error below:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'RequestSubmited'.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-06-14 : 17:41:34

GROUP BY a.Fname, a.Lname, a.MemberID

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-14 : 17:46:04

[code]DECLARE @Tbl1 TABLE (Fname VARCHAR(10), Lname VARCHAR(10), MemberID INT)

INSERT @Tbl1 (Fname, Lname, MemberID)
VALUES
('Smith', 'M', 1),
('David', 'N', 2)

DECLARE @Tbl2 TABLE (MemberID INT, RequestSubmited CHAR(1))

INSERT @Tbl2 (MemberID, RequestSubmited)
VALUES
(1, 'Y'),
(2, 'Y'),
(1, 'Y'),
(1, 'Y'),
(2, 'Y'),
(1, 'Y')

SELECT
A.Fname,
A.Lname,
A.MemberID,
SUM(CASE WHEN B.RequestSubmited = 'Y' THEN 1 ELSE 0 END) AS TotalSubmitted
FROM
@Tbl1 AS A
INNER JOIN
@Tbl2 AS B
ON A.MemberID = B.MemberID
GROUP BY
A.Fname,
A.Lname,
A.MemberID[/code]

inou
Starting Member

9 Posts

Posted - 2012-06-14 : 18:07:38

great, worked! you saved my day.
Thank you very much guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources