0

I've been tasked with pulling a report from a SAGE Accounting database. The SAGE partner have created a view for us to query based on the data tables within SAGE.

The view contains each company (AccountName) plus each user per company, all in one view.

So if I just to a SELECT * FROM I get

  • Company A, 123, 1 Street, , , , T3 3ST, 0, Barry, Jenkins
  • Company A, 123, 1 Street, , , , T3 3ST, 0, Steve, Jenkins
  • Company A, 123, 1 Street, , , , T3 3ST, 0, Accounts, Dept
  • Company B, 456, 2 Street, , , , T1 3ST, 0, John, Jones
  • Company B, 456, 2 Street, , , , T1 3ST, 0, Chris, Jones
  • Company C, 789, 3 Street, , , , T2 3ST, 0, Larry, Paige

However, my query doesn't seem to be responding like a normal table would.

For example the following SQL with a GROUP BY returns 0 records

SELECT 
    AccountName, AccountNumber, AddressLine1, AddressLine2, 
    AddressLine3, AddressLine4, PostCode, AccountBalance 
FROM CustomerView 
GROUP BY AccountName;

Yet this one returns 1000+ rows

SELECT DISTINCT(AccountName), AccountNumber, 
    AddressLine1, AddressLine2, AddressLine3, AddressLine4, 
    PostCode, AccountBalance 
FROM CustomerView;

The problem with using the DISTINCT is that I need at least 1 person record, and when I do eg add CustomerFirstName, I get multiple rows of the same Account Name so I really wanted to be able to GROUP BY.

I ultimately want the first full record of a unique company

Is there something wrong with my SQL Server? Or does the view need looking at?

I've never used a SQL Server view - I usually use MySQL, but I'm fairly sure my syntax is right.

Any help/advice appreciated.

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
Chris A
  • 11
  • 1
  • 2
    Note by the way that `DISTINCT` is not a function. It acts on the whole resultset, so when you add another column you get more rows. So adding brackets just confuses you into thinking it only "distincts" over one column – Charlieface Mar 13 '22 at 17:55

0 Answers0