?
Solved

SQL Query using Group

Posted on 2013-11-15
4
Medium Priority
?
282 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 49

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 49

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 49

Accepted Solution

by:
PortletPaul earned 800 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.

839 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