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
Yong Scott Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Yong Scott Author Commented:
I need to group by the name
0
Ares KurkluSoftware EngineerCommented:
you can use max(name) then.
i think you also want to group by site as well?
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Yong Scott 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
NerdsOfTechTechnology 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Yong Scott 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
Yong Scott 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.