Adding mysql columns

Question

Adding mysql columns

Navigation

#1 by (2 votes)

0

Good afternoon, I opened a question yesterday but I could not express myself correctly, I have the following query

SELECT C.id, C.name as NomePC, C.serial AS Serial, MF.name AS Fabricante, CM.name AS ModeloPC,OS.name AS Sistema, OSV.name AS OSVersao,DP.designation AS Processador, IDH.capacity AS CapacidadeHD, DH.designation AS ModeloHD, DGC.designation AS PlacaVideo, IDM.size AS Memoria, DM.designation AS tipoMemoria FROM glpi_computers AS C INNER JOIN glpi_manufacturers AS MF ON C.manufacturers_id = MF.id INNER JOIN glpi_computermodels as CM ON C.computermodels_id = CM.id INNER JOIN glpi_operatingsystems AS OS ON C.operatingsystems_id = OS.id INNER JOIN glpi_operatingsystemversions AS OSV ON C.operatingsystemversions_id = OSV.id INNER JOIN glpi_items_deviceprocessors AS IDP ON C.id = IDP.items_id INNER JOIN glpi_deviceprocessors AS DP on IDP.deviceprocessors_id = DP.id INNER JOIN glpi_items_deviceharddrives AS IDH on C.id = IDH.items_id INNER JOIN glpi_deviceharddrives AS DH on IDH.deviceharddrives_id = DH.id INNER JOIN glpi_items_devicegraphiccards AS IDGC on C.id = IDGC.items_id INNER JOIN glpi_devicegraphiccards AS DGC ON IDGC.devicegraphiccards_id = DGC.id INNER JOIN glpi_items_devicememories AS IDM on C.id = IDM.items_id INNER JOIN glpi_devicememories AS DM on IDM.devicememories_id = DM.id WHERE C.id = 271 AND IDH.capacity != 0

and I'm getting the following result

WhenthePChasmorethanoneRAMmemorycombIget2queryresultsasshownintheimage,IneedtogetonlyONEresultBUTSOMINGtheamountofmemoryasintheimagebelow(Ihadtoerasetheimagebecauseitdidnothavereputationenoughtopost3images)

HowcanIdothisformysqlitself?

RESOLVED

NowIhaveanotherproblem,Itriedtoreplicatethesolution(usingSUM)intheHD,butitisdoublingthesizeofmyhd,itisonly500gbandasaresultofthequeryIamgettingdouble

mysql

asked by anonymous 27.07.2017 / 19:29

1 answer

C #, Returning a Sql Server query on the Grid View date [closed] Uncaught TypeError: $ (...) .Attr is not a function [closed]

score 2 · Accepted Answer

Just use the SUM(coluna) function in the memory column, and group by the others that will be equal, by Group By

Follow the code below:

SELECT 
C.id, 
C.name as NomePC, 
C.serial AS Serial, 
MF.name AS Fabricante,
 CM.name AS ModeloPC,
OS.name AS Sistema, 
OSV.name AS OSVersao,
DP.designation AS Processador, 
IDH.capacity AS CapacidadeHD, 
DH.designation AS ModeloHD, 
DGC.designation AS PlacaVideo, 
SUM(IDM.size) AS Memoria, 
DM.designation AS tipoMemoria
    FROM glpi_computers AS C
    INNER JOIN glpi_manufacturers AS MF ON C.manufacturers_id = MF.id
    INNER JOIN glpi_computermodels as CM ON C.computermodels_id = CM.id
    INNER JOIN glpi_operatingsystems AS OS ON C.operatingsystems_id = OS.id
    INNER JOIN glpi_operatingsystemversions AS OSV ON C.operatingsystemversions_id = OSV.id
    INNER JOIN glpi_items_deviceprocessors AS IDP ON C.id = IDP.items_id
    INNER JOIN glpi_deviceprocessors AS DP on IDP.deviceprocessors_id = DP.id 
    INNER JOIN glpi_items_deviceharddrives AS IDH on C.id = IDH.items_id
    INNER JOIN glpi_deviceharddrives AS DH on IDH.deviceharddrives_id = DH.id
    INNER JOIN glpi_items_devicegraphiccards AS IDGC on C.id = IDGC.items_id 
    INNER JOIN glpi_devicegraphiccards AS DGC ON IDGC.devicegraphiccards_id = DGC.id
    INNER JOIN glpi_items_devicememories AS IDM on C.id = IDM.items_id
    INNER JOIN glpi_devicememories AS DM on IDM.devicememories_id = DM.id
    WHERE C.id = 271 AND IDH.capacity != 0

    GROUP BY C.id, C.name, C.serial ,  MF.name, CM.name ,  OS.name , 
OSV.name ,  DP.designation ,   IDH.capacity ,  DH.designation , 
DGC.designation ,  DM.designation

Edit:

To sum, hd size, and amount of memory, without grouping by the other columns:

SELECT 
C.id, 
C.name as NomePC, 
C.serial AS Serial, 
MF.name AS Fabricante,
 CM.name AS ModeloPC,
OS.name AS Sistema, 
OSV.name AS OSVersao,
DP.designation AS Processador, 
(Select Sum(IDH.capacity) from glpi_items_deviceharddrives AS IDH where IDH.items_id = C.id) AS CapacidadeHD, 
DGC.designation AS PlacaVideo, 
(Select  SUM(IDM.size) from glpi_items_devicememories AS IDM where IDM.items_id = C.id) AS Memoria
    FROM glpi_computers AS C
    INNER JOIN glpi_manufacturers AS MF ON C.manufacturers_id = MF.id
    INNER JOIN glpi_computermodels as CM ON C.computermodels_id = CM.id
    INNER JOIN glpi_operatingsystems AS OS ON C.operatingsystems_id = OS.id
    INNER JOIN glpi_operatingsystemversions AS OSV ON C.operatingsystemversions_id = OSV.id
    INNER JOIN glpi_items_deviceprocessors AS IDP ON C.id = IDP.items_id
    INNER JOIN glpi_deviceprocessors AS DP on IDP.deviceprocessors_id = DP.id 
    INNER JOIN glpi_items_devicegraphiccards AS IDGC on C.id = IDGC.items_id 
    INNER JOIN glpi_devicegraphiccards AS DGC ON IDGC.devicegraphiccards_id = DGC.id
    WHERE C.id = 271 and (Select Sum(IDH.capacity) from glpi_items_deviceharddrives AS IDH where IDH.items_id = C.id) > 0