Solved

SQL Query using Group

Posted on 2013-11-15
4
270 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

895 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

18 Experts available now in Live!

Get 1:1 Help Now