Calculating the total number of invoices

Question

SELECT VendorState, VendorCity
FROM Vendors JOIN COUNT(*)InvoiceDate as TotalInvoices ON Invoices
WHERE VendorState = 'NV' AND 'MI'

My attempt above wont work at all :/ I want to have it display

VendorState from the Vendors table if the state is NV or MI

VendorCity

and TotalInvoices for each city and at the end in TOTAL drawing the count of it from InvoiceDate from the Invoices table

Answer 1

Try

SELECT count(InvoiceDate) as [NumberInvoices], VendorState, VendorCity FROM Vendors inner JOIN Invoices ON Vendor PK* = Invoices FK WHERE VendorState in ('NV', 'MI') group by VendorState, VendorCity

Answer 2

You can't join tables that way. You need a common value shared between both tables, like a vendor ID or something.

So first, it's:

select a.vendorstate, a.vendorcity, sum(invoices) as 'the sum'
from vendors a inner join invoices b on a.vendorid = b.vendorid
group by a.vendorstate, a.vendorcity
where state in ('NV','MI')

That's similar to your other problem

Answer 3

You need to specify the foreign key on the Invoices table in order to join Vendors to Invoices. Here is an example:

SELECT v.VendorState, v.VendorCity, COUNT(i.InvoiceDate) AS Invoices
FROM Vendors v WITH(NOLOCK)
JOIN Invoices i WITH(NOLOCK) ON i.VendorID = v.VendorID
WHERE v.VendorState IN ('NV', 'MI')
GROUP BY v.VendorState, v.VendorCity
ORDER BY v.VendorState, v.VendorCity

Obviously you will need to change the join i.VendorID = v.VendorID to whatever keys should be here.

Answer 4

Calculating how many invoices were given to each client:

SELECT     clientName, 
           COUNT(InvoiceDate)
FROM       tblClients 
INNER JOIN tblInvoices 
  ON       tblClients.clientID = tblInvoices.ClientID
GROUP BY   clientName;