Count and Inner Join - 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

Count and Inner Join

Author

Topic

Amanda_Nicholls
Starting Member

4 Posts

Posted - 2012-01-25 : 16:50:28

Ok I have done the following

SELECT DISTINCT tbl_Orders.CustomerID, tbl_Customer.CustSurname, tbl_Orders.Quantity
FROM tbl_Orders
INNER JOIN tbl_Customer
ON tbl_Orders.CustomerID=tbl_Customer.CustomerID
WHERE OrderDate<'2012-06-01'

I have this as my output

1 Swallow 1
1 Swallow 2
1 Swallow 20
2 Hunt 1
3 Bailey 1
3 Bailey 3
3 Bailey 10
4 Read 2
4 Read 5
4 Read 12
5 Blatt 1
5 Blatt 2
5 Blatt 6
7 Shelley 2
9 Jessey 1

numbers on the left - Customer ID - then surname then quantity

How do I distinct them all so I just get the one customer ID with their surname and how many orders in total instead of the repetition??

beginner - bit stuck and could do with some help!

Thanks everyone

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 16:56:08

[code]
SELECT tbl_Orders.CustomerID, tbl_Customer.CustSurname, SUM(tbl_Orders.Quantity) AS TotalQty
FROM tbl_Orders
INNER JOIN tbl_Customer
ON tbl_Orders.CustomerID=tbl_Customer.CustomerID
WHERE OrderDate<'2012-06-01'
GROUP BY tbl_Orders.CustomerID, tbl_Customer.CustSurname
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Amanda_Nicholls
Starting Member

4 Posts

Posted - 2012-01-25 : 17:01:50

Legend! Thank you! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 08:52:07

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources