Using count whilst displaying the other columns?

1

Hi I have a table that's been created its basically unique invoices a customer hasn't paid.

Its been created from a series of statements using the main file MainInvoice

What Ive done is a statement like this

SELECT UNINVOICE, COUNT(*) FROM Invoice

This works but only allows me to select the one field UNINVOICE, however theres a few other fields Id like to display but it doesn't work with something like

SELECT UNINVOICE,, COUNT(*), OtherColumn, OtherColumn1, OtherColumn2 but this just flags up an error.

If this is a problem then the 2nd option I have is to somehow link the Invoice table with the MainInvoice one to pull out the fields that I need and then match them using the customer number.

Any advice appricated

2

One way and there may be better ways:

SELECT MT.UNINVOICE, MC.MyCount As [COUNT], MT.OtherColumn, MT.OtherColumn1, MT.OtherColumn2
FROM MyTable MT

LEFT JOIN
(
SELECT UNINVOICE, COUNT(UNINVOICE) As MyCount
)MC ON MT.UNINVOICE = MC.UNINVOICE

3

Another way is to use a window function

SELECT UNINVOICE,
	COL1,
	COL2,
	COUNT(*) OVER(PARTITION BY UNINVOICE) as [COUNTPerUNINVOICE]
FROM Invoice
1 Like