mysql row based calculations.

Question

0.00/5 (No votes)

See more:

ReqColumns\|	hostid\|	name	\|Value
RAM	10093	Total Memory	15.970000
RAM	10093	Total Memory [Free]	12.420000
RAM	10093	Total Memory [used]	3.550000
RAM	10094	Total Memory	3.920000
RAM	10094	Total Memory [Free]	1.970000
RAM	10094	Total Memory [used]	1.950000

The above output I am getting by using this query...

SQL

SELECT ReqColumns,hostid,name , AVG(value_max) AS Value from
(
select sc.ReqColumns,
    i.name,
    TRUNCATE((tu.value_min)/(1024*1024*1024),2) AS value_min,
    TRUNCATE((tu.value_avg)/(1024*1024*1024),2) AS value_avg,
    TRUNCATE((tu.value_max)/(1024*1024*1024),2) AS value_max,
    i.hostid,
    ci.NewInterfaceName,
    lm.LocationName,
    bfm.BusinessFunctionCode,
    bm.BuildingCode,
    FROM_UNIXTIME(tu.clock,'%Y-%m-%d') Date from trends_uint tu

INNER JOIN items i ON i.itemid=tu.itemid
INNER JOIN graphs_items gi ON gi.itemid=i.itemid
  INNER JOIN graphs g ON g.graphid=gi.graphid
INNER JOIN hosts h ON h.hostid=i.hostid

  LEFT JOIN flip_linkutilisation.categorisedinterfaces ci on ci.graphid=g.graphid
  LEFT JOIN flip_linkutilisation.buildingmaster bm ON bm.BuildingID=ci.BuildingID
  LEFT JOIN flip_linkutilisation.businessfunctionmaster bfm ON bfm.BusinessFunctionID=bm.BusinessFunctionID
  LEFT JOIN flip_linkutilisation.locationmaster lm ON lm.LocationID=bm.LocationID
  LEFT JOIN flip_linkutilisation.servercolumns sc ON sc.ColumnDetails=g.name
where tu.itemid IN(SELECT itemid FROM items  WHERE  name in ('Total Memory','Total Memory [Free]', 'Total Memory [used]') )
) as x group by hostid,name

Here i need one row for one hostid and the value column calculation willl be the (Total Memory[used]/Total Memory)*100

That means my output should be like this

ReqColumns\|	hostid\|	name	\|Value-%
RAM	10093	Memory Details	22.22
RAM	10094	Memory Details	49.74

Please help me to solve this..

Thanks in advance

Posted 12-Nov-13 20:24pm

kanamala subin

Updated 12-Nov-13 21:17pm

phil.o

v3

Add a Solution

Comments

Thava Rajan 13-Nov-13 2:35am

what is the value % is it the total space user

kanamala subin 13-Nov-13 3:09am

value % is the TotalMemory[used]/Total Memory*100

Actually this is a RAM inside a server...
Like that many servers are there..
So i want to make a report about RAM utilisation in Percentage

So we can tell in a particular hostid RAM is used this much..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thava Rajan · Answer 1 · 2013-11-12T22:27:00

SELECT
	ReqColumns, hostid, name, (TotalUsed/TotalMemory)* 100 AS Value
FROM
	(
		SELECT
			sc.ReqColumns, i.hostid, i.name, 
			/*TRUNCATE((tu.value_min) / (1024 * 1024 * 1024), 2) AS value_min, 
			TRUNCATE((tu.value_avg) / (1024 * 1024 * 1024), 2) AS value_avg,
			TRUNCATE((tu.value_max) / (1024 * 1024 * 1024), 2) AS value_max,*/
			SUM(IF(i.name='Total Memory [used]',TRUNCATE((tu.value_max) / (1024 * 1024 * 1024), 2) ,0)) AS TotalUsed,
			SUM(IF(i.name='Total Memory',TRUNCATE((tu.value_max) / (1024 * 1024 * 1024), 2) ,0)) AS TotalMemory
			 
		FROM
			trends_uint tu
			INNER JOIN items i
				ON   i.itemid = tu.itemid
			INNER JOIN graphs_items gi
				ON   gi.itemid = i.itemid
			INNER JOIN graphs g
				ON   g.graphid = gi.graphid
			INNER JOIN HOSTS h
				ON   h.hostid = i.hostid
                        LEFT JOIN flip_linkutilisation.servercolumns sc 
                                ON   sc.ColumnDetails = g.name
		WHERE
			i.name IN ('Total Memory', 'Total Memory [Free]', 
			              	        'Total Memory [used]')
		GROUP BY sc.ReqColumns, i.hostid, i.name
	) AS x

well i think this will solve your problem?

watch out i remove the unnecessary joins and columns and reduce the number of reads