Calculate the report summary base on two table PHP MYSQLI

This my selection code put inside my php
$sql = "select site,name,
        SUM(cpu_count) as vcpu,
        SUM(memory_size_ingb) AS mem_gb,
        SUM(disk_space_size_ingb) as storage_gb,
        COUNT(date)as daycount,
        (SUM(cpu_count)*(select vcpu from price_tag order by site)+SUM(memory_size_ingb)*(select memory from price_tag order by site)+SUM(disk_space_size_ingb)*(select storage from price_tag order by site))*(count(date)/31) as 'total'		
FROM nov
GROUP BY name";

Open in new window

Error getting :
Warning: mysqli_query(): (21000/1242): Subquery returns more than 1 row in C:\xampp\htdocs\db_v4\gen_rep.php on line 80
ERROR: Could not able to execute select site,name, SUM(cpu_count) as vcpu, SUM(memory_size_ingb) AS mem_gb, SUM(disk_space_size_ingb) as storage_gb, COUNT(date)as daycount, (SUM(cpu_count)*(select vcpu from price_tag order by site)+SUM(memory_size_ingb)*(select memory from price_tag order by site)+SUM(disk_space_size_ingb)*(select storage from price_tag order by site))*(count(date)/31) as 'total'      FROM nov GROUP BY name. Subquery returns more than 1 row

Because my report count the sum from the nov table then the total costs are refer to the pricing table look like the picture do.

Actually I want to do is calculate site from nov table automatic detect the site in pricing table ,example I calculate the summary report of "BNG(site)" in nov table then automatic detect "BNG(site)" in pricing Table,but i prefer no hardcoded because I got another document where the site is different value
Capture.PNG
Captur2e.PNG
Scott Yong Asked:
Who is Participating?
 
NerdsOfTechConnect With a Mentor Technology ScientistCommented:
If Captur2e is the end result you must group by `site` not `name`:

$sql = "
 SELECT 
   site
 , SUM(cpu_count) AS vcpu,
 , SUM(memory_size_ingb) AS mem_gb,
 , SUM(disk_space_size_ingb) AS storage_gb,
 , COUNT(date) AS daycount
 , (
    SUM(cpu_count)*(select vcpu from price_tag order by site) +
    SUM(memory_size_ingb)*(select memory from price_tag order by site) + 
    SUM(disk_space_size_ingb)*(select storage from price_tag order by site)
   ) * (count(date)/31) 
    AS `total`
 GROUP BY site
";

Open in new window

1
 
Ares KurkluSoftware EngineerCommented:
Looks like you don't need to know about the "name" field in the query, why don't you just remove it ?
2
 
Scott Yong Author Commented:
I need to group by the name
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Ares KurkluSoftware EngineerCommented:
you can use max(name) then.
i think you also want to group by site as well?
0
 
Scott Yong Author Commented:
yes.. because i need refer site on both table
0
 
Ares KurkluSoftware EngineerCommented:
This is a bit confusing when the site is BNG does the site always start with BNG as well ? do you want to group by name or site ? isn't the Captur2e the end result you want?
0
 
Scott Yong Author Commented:
If i want to create new table to insert this all into it as column how was it?
0
 
NerdsOfTechTechnology ScientistCommented:
If i want to create new table to insert this all into it as column how was it?
This would be a separate question; however, to help point you in the right direction: you would use the SELECT query above as a subquery for an INSERT INTO --- ON DUPLICATE KEY UPDATE query. I recommend putting the values into their own column and having the `site` be your key for such a derived recordset.
0
 
Scott Yong Author Commented:
INSERT INTO --- ON DUPLICATE KEY UPDATE
on duplicate key update is to remove duplicate?
0
 
NerdsOfTechTechnology ScientistCommented:
INSERT INTO --- ON DUPLICATE KEY UPDATE means that if a record exists with that key in the table, update that record; otherwise, add the record to the table.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.