Using Count from different tables

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

SQL Server 2005 Forums

Transact-SQL (2005)

Using Count from different tables

Author

Topic

phopkinson
Starting Member

2 Posts

Posted - 2008-08-07 : 09:26:48

I have this query below.....

SELECT tbl_Resellers.int_ResellerID, tbl_Resellers.str_FirstName, tbl_Resellers.str_LastName, tbl_Resellers.bit_status,
COUNT(tbl_orders.int_OrderID) AS TotalOrders,
SUM(tbl_orders.int_orderSubTotal) AS TotalSpend,
COUNT(tbl_customers.int_CustomerID) AS TotalCustomers

FROM tbl_Resellers INNER JOIN tbl_orders ON
tbl_Resellers.int_ResellerID = tbl_orders.int_ResellerID LEFT JOIN tbl_Customers ON
tbl_Resellers.int_ResellerID = tbl_Customers.int_ResellerID

GROUP BY tbl_Resellers.int_ResellerID, tbl_Resellers.str_FirstName, tbl_Resellers.str_LastName, tbl_Resellers.str_username,
tbl_Resellers.str_emailAddress, tbl_Resellers.str_company, tbl_Resellers.dtm_signupDate,
tbl_Resellers.bit_status, tbl_Resellers.int_commBal,
tbl_Resellers.str_lastLogin

The results of the COUNTS are wrong though. If the value of TotalCustomers is greater the 0 it is always the sames as the value of TotalOrder

I imagine the problem is something to do with the FROM statement but I cant figure it out

Any help much apprecaited

Thanks

Paul

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-07 : 09:28:56

try using... count(distinct your_ID_Col)

Em