Simple SQL help needed

[SharkAttack]

I am running a query, I have 3 fields, Client ID, Client Name and a Sum of various billing totals. Now I need these billing totals to be grouped for the client, so I am grouping on the Client ID.

Now I can't group on name at all as some clients will have changed their name at one point or another, and that would split the grand total into 2 or 3 seperate lines...Can't be having that!

I thought that I could avoid this by placing Client Name in the order by clause along with the Client Total but it isn't having it! Client Name must be entered in an aggregate function or group by clause. Gah!

There must be a simple way around this? I have never been in a situation where this has troubled me before. Lucky me! Till now...

[SharkAttack]

Right well I stuffed the Max function around Client Name and it works for now. If there is a better way around it then let me know.

Thanks

[davejm]

Nah thats the best way to do it mate.

[kona]

The only problem with that is that for each client you will get the greatest name (in dictionary sorting), instead of the current name. If that is not a problem, then keep the max as it's simple. Otherwise, post your query and I'll tell you how to change it.

It sounds like your schema is pretty nasty, you shouldn't be having this problem normally. If it is within your power to change it, tell me what the tables look like right now so I can give you some tips on improving them.

[davejm]

What kona said.

A clientID should only have 1 current ClientName and other tables should use the ClientID as a foreign key linking the tables. That way, if a ClientName changes there aren't reprocussions because the tables are linked with the ID and not the name.

If you need to keep a history of client name changes, you'll need another table called ClientName which links to the Client table via the ClientID. You can then have more than one name per client, obviously the newest one added being the current record.

Hope I've made sense!

[MartyG]

You could get around it by creating a temporary table

CREATE TABLE #Temp
(ClientID int,
Clientname varcar(xxx),
Sum_whatever int
....
)

Then select into the #Temp table

INSERT INTO #Temp
SELECT <your query>

(obviously the select needs to return the same number of fields as the Temp table contains, but you can do stuff like SELECT ClientID,'',Sum( cost ) etc )

Then update the name based on the client ID


UPDATE #Temp
SET #Temp.ClientName = Clients.Name
FROM #Temp,Clients
WHERE #Temp.ClientID = Clients.ClientID


Then simply SELECT * FROM #Temp

Hey presto - the data you want. There's probably a simpler way of doing it if I had a further think, that's off the top of my head.

If you're having this problem though, I suspect your data isn't normalised properly. You should ideally key to Client data (name, addy etc) in a seperate table.

Regards
Marty

[SharkAttack]

After reading this I am of course being a complete muppet. I should just link into the tblClients in my query rather than using Client Name from the BillingRecords table (which is used in case billing address is different).

Dum...bo.

[choddo]

hehe. correct Funny how you don't see these things until you bounce ideas off a few other people though, your brain focusses on the immediate problem like trying to get a fire lit to cook your mammoth steak and it's really hard to look at it more broadly.