Update table based on column grouping

Question

Post reply

Update table based on column grouping

DerbyNeal

Hall of Fame

Points: 3854
More actions
August 21, 2012 at 9:33 am

#265638

Hi,
I have a following table (table1) which needs several updates. All the updates are grouped by the 'phone' column:
Id, phone, phonecount, contactname, contactnamecount, tablesource
1, 003, 0, Mr Smith, 0, contactperson
2, 003, 0, Smith P, 0, customer
3, 003, 0, Smith P, 0, customer
4, 003, 0, Gary Hill, 0, employee
5, 004, 0, ., 0, contactperson
6, 004, 0, T Gray, 0, customer
7, 005, 0, S Marlow, 0, contact
8, 005, 0, S Marlow, 0, customer
9, 006, 0, N Test, 0, employee
Firstly, the phonecount needs to be updated, where the max(id) is the count of each unique phone’s (i.e. grouped by phone)
Next, the contactnamecount needs to be updated, where the max(id) is the count of unique contactname’s (grouped by phone).
To be shown as:
Id, phone, phonecount, contactname, contactnamecount, tablesource
1, 003, 0, Mr Smith, 0, contactperson
2, 003, 0, Smith P, 0, customer
3, 003, 0, Smith P, 0, customer
4, 003, 4, Gary Hill, 3, employee
5, 004, 0, ., 0, contactperson
6, 004, 2, T Gray, 2, customer
7, 005, 0, S Marlow, 0, contact
8, 005, 2, S Marlow, 1, customer
9, 006, 1, N Test, 1, employee
Any ideas?
Thanks in advance,

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

That is easily achieved if you follow: http://www.sqlservercentral.com/articles/Best+Practices/61537/

You're not first-timer, aren't you? 😉

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 2

Hi Eugene,

Thank you very much for your posting. Hope this is clearer:

declare @PhoneTable_New table

(

Id BIGINT IDENTITY(1,1) NOT NULL,

Phone nvarchar(20),

PhoneCount Int,

ContactName nvarchar(60),

ContactNameCount Int,

CustID nvarchar(20) ,

CustIDCount Int,

CustomerName nvarchar(60),

CustomerNameCount Int,

TableSource nvarchar(20),

PhoneSource nvarchar(20)

)

INSERT INTO @PhoneTable_New

(phone, phonecount, contactname, contactnamecount, tablesource)

SELECT '003', '0', 'Mr Smith', '0', 'contactperson' UNION ALL

SELECT '003', '0', 'Smith P', '0', 'customer' UNION ALL

SELECT '003', '0', 'Gary Hill', '0', 'employee' UNION ALL

SELECT '004', '0', '.', '0', 'contactperson' UNION ALL

SELECT '004', '0', 'T Gray', '0', 'customer' UNION ALL

SELECT '005', '0', 'S Marlow', '0', 'contact' UNION ALL

SELECT '005', '0', 'S Marlow', '0', 'customer' UNION ALL

SELECT '006', '0', 'N Test', '0', 'employee'

PS - Do you mean "You're not a first timer, are you?"? 😉

Thanks,

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Here's one option

UPDATE o SET

phonecount = g.phonecount,

contactnamecount = g.contactnamecount

FROM @PhoneTable_New o

JOIN (

SELECTMAX(ID) max_id,

phone,

COUNT(phone) phonecount,

COUNT( DISTINCT contactname) contactnamecount

FROM @PhoneTable_New

GROUP BY phone ) g ON o.phone = g.phone AND o.id = g.max_id

SELECT id, phone, phonecount, contactname, contactnamecount, tablesource

FROM @PhoneTable_New

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 4

DerbyNeal

Hall of Fame

Points: 3854

August 21, 2012 at 10:18 am

#1528227

Many thanks Luis

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

You're welcome.

Be sure to understand the code before you use it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 6

Can someone help further please?

I need to update an additional column 'CustomerName' within the table. How do I count the TableSource for each Phone (for example: In the above postings there are 2 'Customer' where phone = '003'; 1 'Customer' for '004'; and 1 'Customer' for '005')

Thanks in advance,

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

Could you post the desired output?

I couldn't understand how you want it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 8

Hi Luis,

Essentially I need to update the 'CustomerName' column based on the phone grouping where the phonecount <> 0 (see Max(Id) in your 1st post) .

If there are more than 1 'Customer' in the 'TableSource' column (per phone group) then the 'CustomerName' value is set to 'MULTIPLE COMPANIES', otherwise, if only 1 occurence is found, then that value (i.e. 'T Gray') should populate the 'CustomerName' column.

This is an example of the desired result:

declare @PhoneTable_New table

(

Id BIGINT IDENTITY(1,1) NOT NULL,

Phone nvarchar(20),

PhoneCount Int,

ContactName nvarchar(60),

ContactNameCount Int,

CustID nvarchar(20) ,

CustIDCount Int,

CustomerName nvarchar(60),

CustomerNameCount Int,

TableSource nvarchar(20),

PhoneSource nvarchar(20)

)

;

INSERT INTO @PhoneTable_New

(phone, phonecount, contactname, contactnamecount, tablesource, CustomerName)

SELECT '003','0','Mr Smith','0','contactperson', '' UNION ALL

SELECT '003','0','Smith P','0','customer', '' UNION ALL

SELECT '003','4','Gary Hill','3','employee', 'MULTIPLE COMPANIES' UNION ALL

SELECT '004','0','.','0','contactperson', '' UNION ALL

SELECT '004','2','T Gray','2','customer', 'T Gray' UNION ALL

SELECT '005','0','S Marlow','0','contact', '' UNION ALL

SELECT '005','2','S Marlow','1','customer', 'S Marlow' UNION ALL

SELECT '006','1','N Test','1','employee', '' UNION ALL

SELECT '007','0','Test','0','customer', '' UNION ALL

SELECT '007','2','T T','1','contact', 'Test'

;

select * from @PhoneTable_New

;

Hope that is clear.

Thanks,

Luis Cazares SSC Guru Points: 183698 More actions · Answer 9

I'm not sure if this is the best approach or if you should divde the query for better performance, but as I said in my previous post: "It's an option"

UPDATE o SET

phonecount = g.phonecount,

contactnamecount = g.contactnamecount,

CustomerName = g.CustomerName

FROM @PhoneTable_New o

JOIN (

SELECTMAX(ID) max_id,

phone,

COUNT(phone) phonecount,

COUNT( DISTINCT contactname) contactnamecount,

CASE WHEN SUM( CASE WHEN tablesource = 'customer' THEN 1 ELSE 0 END) > 1

THEN 'MULTIPLE COMPANIES'

ELSE MAX(CASE WHEN tablesource = 'customer' THEN contactname ELSE NULL END) END CustomerName

FROM @PhoneTable_New

GROUP BY phone ) g ON o.phone = g.phone AND o.id = g.max_id

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86908 More actions · Answer 10

So what happens next when someone comes along and runs:

INSERT INTO @PhoneTable_New

(phone, phonecount, contactname, contactnamecount, tablesource)

SELECT '003', '0', 'Mr Jones', '0', 'contactperson'

Do you also need a query that removes the previous counts from phone 003 and puts them on Mr Jones' record?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 11

Thanks Luis & Dwaine for your help.

Dwain - I Don't need to cater for that, as the table query is refreshed each day (It is a snap-shot query).

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 12

I do have another query though...

I have a table (@PTable) which needs 2 updates performed on it.

Firstly, the records are grouped by 'Phone'. I need a distinct count on the TableSource column where the value is 'contactperson'

(for each phone group). If there is only 1 'contactperson' found then that value must populate the ContactName record where PhoneCount > 0.

If more one record is found then 'MULTIPLE NAMES' should be entered in the ContactName where PhoneCount > 0, otherwise it is left as ''.

Secondly, the SourceCount column should be the Distinct Count of each GenericName (where TableSource = 'contactperson') for each phone group.

declare @Ptable table

(

Id BIGINT IDENTITY(1,1) NOT NULL,

Phone nvarchar(20),

PhoneCount Int,

GenericName nvarchar(60),

CustomerName nvarchar(60),

TableSource nvarchar(20),

SourceCount Int

)

INSERT INTO @Ptable

(phone, phonecount, GenericName, ContactName, TableSource, SourceCount)

SELECT '003', '0', 'JOHN', '', 'contactperson', '0' UNION ALL

SELECT '003', '0', 'JF BELLAMY', '', 'custtable', '0' UNION ALL

SELECT '003', '0', 'JOHN', '', 'contactperson', '0' UNION ALL

SELECT '003', '0', 'JF BELLAMY', '', 'custtable', '0' UNION ALL

SELECT '003', '5', 'GERRY', '', 'empltable', '0' UNION ALL

SELECT '004', '0', 'PETE', '', 'contactperson', '0' UNION ALL

SELECT '004', '0', 'P BALL', '', 'custtable', '0' UNION ALL

SELECT '004', '0', 'DAVE', '', 'contactperson', '0' UNION ALL

SELECT '004', '4', 'ALAN', '', 'empltable', '0' UNION ALL

SELECT '005', '1', 'SAM', '', 'contactperson', '0' UNION ALL

SELECT '006', '1', 'TERRY', '', 'empltable', '0'

select * from @Ptable

After the updates are performed, the result should be:

INSERT INTO @Ptable

(phone, phonecount, GenericName, ContactName, TableSource, SourceCount)

SELECT '003', '0', 'JOHN', '', 'contactperson', '0' UNION ALL

SELECT '003', '0', 'JF BELLAMY', '', 'custtable', '0' UNION ALL

SELECT '003', '0', 'JOHN', '', 'contactperson', '0' UNION ALL

SELECT '003', '0', 'JF BELLAMY', '', 'custtable', '0' UNION ALL

SELECT '003', '5', 'GERRY', 'JOHN', 'empltable', '1' UNION ALL

SELECT '004', '0', 'PETE', '', 'contactperson', '0' UNION ALL

SELECT '004', '0', 'P BALL', '', 'custtable', '0' UNION ALL

SELECT '004', '0', 'DAVE', '', 'contactperson', '0' UNION ALL

SELECT '004', '4', 'ALAN', 'MULTIPLE NAMES', 'empltable', '2' UNION ALL

SELECT '005', '1', 'SAM', 'SAM', 'contactperson', '1' UNION ALL

SELECT '006', '1', 'TERRY', '', 'empltable', '0'

Any ideas please?

Thanks in advance,

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

DerbyNeal (8/22/2012)
Thanks Luis & Dwaine for your help.
Dwain - I Don't need to cater for that, as the table query is refreshed each day (It is a snap-shot query).

It may well be easier to generate these figures in the "snap-shot query" rather than spooling the output of the query into a table and updating the table. Why not post the "snap-shot query" so folks can determine if this is the case?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183698 More actions · Answer 14

DerbyNeal (8/22/2012)
I do have another query though...
Any ideas please?
Thanks in advance,

I have an idea.

Why don't you try to use the previous queries as example and show us what you can do. We're not here to do your job.

I'm sorry if this seems rude, but the queries are very similar and if you can't manage to change them for this new requirement, you might probably aren't understanding the code and what it's doing.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2