Solved

SQL Query using Group

Posted on 2013-11-15
4
267 Views
Last Modified: 2013-11-15
What is the needed Query statement to find the total number of orders per person

Given:

Table A:
ID      OrderNumber
___________________
1      X123
2      X213
1      X345
2      x237
3      x554
4      x435
1      x333

Table B:
ID      FName      Lname
_____________________
1      Jon         Smith
3      Al            Berry
2      Sue        Jones
4      Jack        Jill

Result Table:
Name            Count
_____________________
Jon Smith      3
Sue Jones      2
Al Berry         1
Jack Jill           1
0
Comment
Question by:williambhowardjr
  • 3
4 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39652685
select b.FName , b.Lname, count(*) as Count_of
from table_b as b
inner join table_a as a on b.id = a.id
group by b.FName , b.Lname
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39652688
or:

select b.FName + ' ' + b.Lname as FullName, count(*) as Count_of
from table_b as b
inner join table_a as a on b.id = a.id
group by b.FName + ' ' + b.Lname
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 200 total points
ID: 39652695
by the way, if you want all names, then the count of the orders placed
then use either of the above

however, if you are only interested in those names that have ordered, then reverse the table sequence e.g.

select b.FName + ' ' + b.Lname as FullName, count(*) as Count_of
from table_a as a
inner join table_b as b on a.id = b.id
group by b.FName + ' ' + b.Lname
0
 

Author Closing Comment

by:williambhowardjr
ID: 39652703
Works Great - Thanks for the Help.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now