7 Replies - 1056 Views - Last Post: 08 October 2012 - 06:03 AM

#1 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Counting and updating values

Posted 08 October 2012 - 03:06 AM

Good morning guys I have a problem can maybe assist.

I have a table

KPI_table with values like this

ID	Perf_ID 	Name	Weight	Total Weight
1	102	        Blah 1   1	        2
2	102	        Blah 2	 1	        2
3	201	        Blah 3	 1	        3
4	201	        Blah 4 	 1	        3
5	201	        Blah 5	 1	        3


Look at the last colum this value must be counted from the same ID by 2nd colum named Perf_ID.

If Perf_ID is 500 and there is 3 of them the colums on the right total weight value must be 3 ect now I must run this statment on the hole table of 10000 enteries will it be posible to select and update the table with the nessasary values

Regards
Thinus

This post has been edited by Atli: 08 October 2012 - 04:21 AM
Reason for edit:: [code] tags also work great for formatted text.


Is This A Good Question/Topic? 0
  • +

Replies To: Counting and updating values

#2 NathanMullenax  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 83
  • View blog
  • Posts: 176
  • Joined: 23-September 12

Re: Counting and updating values

Posted 08 October 2012 - 03:29 AM

I think something like this would work, if I'm reading the question right.

UPDATE KPI_table A
SET    totalWeight = (SELECT SUM(weight) 
                      FROM KPI_table B
                      WHERE B.perf_ID=A.perf_ID)

Was This Post Helpful? 0
  • +
  • -

#3 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Re: Counting and updating values

Posted 08 October 2012 - 03:52 AM

It gives error says incorrect syntex near 'A'
Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3716
  • View blog
  • Posts: 5,976
  • Joined: 08-June 10

Re: Counting and updating values

Posted 08 October 2012 - 04:33 AM

Why is it, exactly, that you need this column to actually exist? Where do you query it's value?

It would make more sense, generally speaking, to query the table to get the total weight. You already have all the required data stored in the table, why store the sum as well? It's basically just duplicate data, which is usually best to avoid.

You can query the sum easily enough:
SELECT Perf_ID, SUM(Weight) AS Total_Weight
FROM KPI_table
GROUP BY Perf_ID


Was This Post Helpful? 1
  • +
  • -

#5 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Re: Counting and updating values

Posted 08 October 2012 - 04:45 AM

Atli I did not write the program that uses these database and the way it is constructed and the head IT is gone for next two weeks so I basically screwed.

But all I know this is the way the program function-ate and it breaks if the data is not in the format Requested.

But your query works wonderful it is what I need now just must can update the table with it so that by each corresponding Perf_ID it must put the sum value selected by your statement into the Total Weight column is this possible?

I just graduated and is still little dumb with these queries?

Thanks for help
Was This Post Helpful? 0
  • +
  • -

#6 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3716
  • View blog
  • Posts: 5,976
  • Joined: 08-June 10

Re: Counting and updating values

Posted 08 October 2012 - 05:39 AM

OK, I see.

Well, you should be able to use a join to accomplish this. Something along the lines of:
UPDATE KPI_table SET
	Total_Weight = tw
FROM KPI_table
INNER JOIN (
	SELECT Perf_ID, SUM(Weight) AS tw
	FROM KPI_table
	GROUP BY Perf_ID
) AS ot
	ON KPI_table.Perf_ID = ot.Perf_ID



Keep in mind, though, that you'll need to execute this every time a you enter a row, or update the weight of an existing row. That would be one of the disadvantages of this kind of data duplication.
Was This Post Helpful? 1
  • +
  • -

#7 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Re: Counting and updating values

Posted 08 October 2012 - 05:46 AM

Atli

Dude you are a genius exactly what I needed thanks so so much you rally just saved my ass from my boss

I will definitely keep this in mind may I ask where can I get tutorials or information on MS SQL queries where I can learn more about them?

Regards and Thanks
Thinus
Was This Post Helpful? 0
  • +
  • -

#8 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3716
  • View blog
  • Posts: 5,976
  • Joined: 08-June 10

Re: Counting and updating values

Posted 08 October 2012 - 06:03 AM

The MSDN docs are by far the best resource about MSSQL statements. Just Google "MSDN SELECT", for example, to find the MSDN docs for the SELECT statement. If you look at the docs for the UPDATE statement, you'll find various different examples using joins, similar to what I did.


One more thing I'd like to point out. You can fake the structure you are after by defining a view, and then query the view instead of the real table. For instance:
CREATE VIEW KPI_table_with_total AS
SELECT ID, Perf_ID, Weight, (
	SELECT SUM(Weight) FROM KPI_table
	WHERE Perf_ID= ot.Perf_ID
	GROUP BY Perf_ID
) AS total_weight
FROM KPI_table AS ot;


This is far from being the most efficient thing in the world, but it's a whole lot less annoying than having to UPDATE the table each time it's changed.

All you'd have to do after defining that view is query the KPI_table_with_total view like you would have the KPI_table table, and it will fake the total_weight value as if it actually existed.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1