MySQL queries

In this project you will create four MySQL queries. The schema is below, I have attached an EER, and a database dump including sample data will be provided on project acceptance.

We have a database that stores records of earnings and expenses for various machines. For earnings, half of the machines show their earnings as a single amount, e.g. $1,250.25. The other half show their earnings as two values, CoinsLeft and CoinsRight. If CoinsLeft is 1205 and CoinsRight is 962 the dollar amounts would be $301.25 and $240.50, (1205 / 4) and (962 / 4) for a total of $541.75.

Half of the time, amounts recorded are the cumulative total amount earned over the lifetime of the machine. The other half of the time, the amount is just the amount shown, and in these cases the BOOL field ResetEarnings is TRUE.

Queries needed for this project:

1) For a specified date range, show the sum of TotalEarnings OR the total of (CoinsLeft and CoinsRight) for each idMachine. Group results by Location.

If a given idMachine's CountsCoins is TRUE, calculate the total of (CoinsLeft + CoinsRight) / 4 ), otherwise calculate TotalEarnings.

In all cases, you must subtract the last entry's amount from the current entry, EXCEPT the first entry which is not counted in the sum.

However, if a given TotalEarnings row has a TRUE ResetEarnings field, do not subtract the prior entry's earnings and instead add the full amount of TotalEarnings or (CoinsLeft + CoinsRight / 4) for that entry.

Example 1:

idMachine, date, TotalEarnings, ResetEarnings

1, 1/1/2018, 100, 0

1, 1/7/2018, 150, 0

1, 1/14/2018, 250, 0

1, 1/21/2018, 20, 1

1, 1/28/2018, 120, 0

For date range 1/1/2018 - 1/7/2018 the total for idMachine 1 would be: (150 - 100) = $50

For date range 1/1/2018 - 1/14/2018 the total for idMachine 1 would be: (150 - 100) + (250 - 150) = $150

For date range 1/1/2018 - 1/21/2018 the total for idMachine 1 would be: (150 - 100) + (250 - 150) + (20 - 0) = $170

For date range 1/1/2018 - 1/28/2018 the total for idMachine 1 would be: (150 - 100) + (250 - 150) + (20 - 0) + (120 - 20) = $270

Example 2:

idMachine, date, ResetEarnings, CountsCoins, CoinsLeft, CoinsRight

2, 1/1/2018, 0, 1, 4, 8

2, 1/7/2018, 0, 1, 16, 21

2, 1/14/2018, 1, 1, 8, 16

2, 1/21/2018, 0, 1, 10, 20

For date range 1/1/2018 - 1/7/2018 the total for idMachine 2 would be: (16 - 4) + (21 - 8) / 4 = $6.25

For date range 1/1/2018 - 1/14/2018 the total for idMachine 2 would be: (6.25) + ((8 + 16) / 4 )) = $12.25

For date range 1/1/2018 - 1/21/2018 the total for idMachine 2 would be: ($12.25) + (10 - 8) + ((20 - 16) / 4 ) = $13.75

2) For a specific date range, show the sum of Expenses per idMachine, grouped by Location.

3) For a specific date range, show the /net/ (e.g. the result of query 1 minus the result of query 2) per idMachine, grouped by location, WITH a net sum of the machines in each location.

4) For a specific date range, show the number of plays per idMachine, grouped by location. Similar to TotalEarnings, you must subtract the number of plays from the previous entry, and total plays can also be reset when the ResetPlays field is TRUE.



Machine ID

Machine name, text

Date purchased

Cost of purchase, integer

CountsCoins, bool

Serial number, text






Expense, text


TotalExpense, float

[login to view URL]

[login to view URL]



Totalplays, integer


[login to view URL]

[login to view URL]



ResetEarnings, bool

Coins left, integer

Coins right, integer

TotalEarnings, float


[login to view URL]

[login to view URL]

Beceriler: Veri Tabanı Yönetimi, MySQL, SQL

Daha fazlasını gör: Simple script for scraping Angular based website and storing a small chunk of data in MySQL database., simple mysql database template, simple mysql database admin panel, mysql, sql, organization chart php mysql left right, python script create simple mysql database, simple mysql database lead capture reporting, select mysql database create plot, simple mysql database application iphone, php mysql database create reports, create mysql database simple invoice, simple mysql database application, information mysql database create document, table reporting mysql database web based java, whm cpanel skel mysql database create account, mysql help right, simple mysql database, oscommerce mysql queries pproducts_model, write queries mysql

İşveren Hakkında:
( 13 değerlendirme ) Portland, United States

Proje NO: #16263004



I am an experience database adminstration developer. I read your situation and I can help you in this. Please message me for further discussion. I will charge the minimum as possible,

%selectedBids___i_period_sub_7% gün içinde 30%project_currencyDetails_sign_sub_9% %project_currencyDetails_code_sub_10%
(5 Değerlendirme)

Bu iş için 43 freelancer ortalamada $146 teklif veriyor


Hi , I can start immediately to write mySQL queries as per the requirement. I am a highly motivated professional expert in programming and design. I will go with you until the queries are 100% accurate and also you can Daha Fazla

in %bids___i_period_sub_35% gün içinde200%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(48 Değerlendirme)
in %bids___i_period_sub_35% gün içinde100%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(186 Değerlendirme)
in %bids___i_period_sub_35% gün içinde155%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(34 Değerlendirme)

Hello sir I hope you are doing well. After reading your offer, this looks like a perfect fit for my skill sets so May I discuss with you for further details about this project? I'm a web developer with over 6 Daha Fazla

in %bids___i_period_sub_35% gün içinde250%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(30 Değerlendirme)

Hi there I am MySQL expert.i can help [login to view URL] contact me

1 gün içinde %bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% USD
(55 Değerlendirme)

Hi there, I have read the details and checked attached screenshot of UML. I have strong expertise in plsql, pivot tables, dynamic queries, normalization, umls, triggers, procedures and more. Have a look at my profile Daha Fazla

in %bids___i_period_sub_35% gün içinde260%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(20 Değerlendirme)

I am mysql dba having more then 10 yrs of exp............i am expert in performance tunning,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

in %bids___i_period_sub_35% gün içinde111%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(62 Değerlendirme)

Hi, I have read your job description. I can definitely do it. I can give you fully responsive website design. I have done many projects using Codeigniter, php, wordpress etc. I can give you the exact what you want.

in %bids___i_period_sub_35% gün içinde100%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(21 Değerlendirme)

Hello Sir, hope you are fine, I am interested in your work "MySQL queries". I am ready to write quires from given ERD. i need MYSQL recording for testing the queries in live database ?

in %bids___i_period_sub_35% gün içinde150%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(18 Değerlendirme)
1 gün içinde %bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% USD
(17 Değerlendirme)

Thanks for your job posting. I have read carefully looked at your project requirements and I can do best job. If you have desire to work with me, please contact me not to miss out the chance. Thanks.

in %bids___i_period_sub_35% gün içinde155%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(8 Değerlendirme)

Hi there! Allow me to complete this task. Please ping me so we can discuss more and I can start off my work right away :)

in %bids___i_period_sub_35% gün içinde150%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(11 Değerlendirme)

Dear Sir! I am expert in mysql. Please check my reviews. I have ever developed the project which is similar your project. I'm sure you will see my high skill, once you select me. I hope we'll have a long term good r Daha Fazla

in %bids___i_period_sub_35% gün içinde100%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(17 Değerlendirme)

I have read through your project and I am incredibly interested in taking on your project. I am an experienced developer with a diploma in the field of web development.. I have been working in the field of web developm Daha Fazla

in %bids___i_period_sub_35% gün içinde155%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(24 Değerlendirme)

Hi, A SQL Server, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS) and SQL Server Analysis Services (SSAS) expert here. Please feel free to discuss the project with me so that I can wrap Daha Fazla

in %bids___i_period_sub_35% gün içinde250%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(2 Değerlendirme)
in %bids___i_period_sub_35% gün içinde333%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(7 Değerlendirme)

Hi. I am a developer with over 8 years experience. Please have a look on my profile. I hope a long time relationship so I will try my best. I am ready to start. Please hire me. best regards

in %bids___i_period_sub_35% gün içinde150%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(3 Değerlendirme)

Hello, I am interested in your job, it will be done in few hours. Further, once I will complete the queries I will get a feedback from you and incase of any issue with outcome I will resolve the matter. Let's chat to d Daha Fazla

1 gün içinde %bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% USD
(3 Değerlendirme)

Greetings!!! I can start immediately I'm a professional Web developer with more than 6 Years experience in php, laravel, magento, wordpress and all types of designing work. I have very good Knowledge and clear u Daha Fazla

in %bids___i_period_sub_35% gün içinde100%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(7 Değerlendirme)

Good afternoon, my name is Franco, i am web developer whit strong knowledge of sql language, so, we would be happy to collaborate with you so that your project be done in the best way. We are available for any quest Daha Fazla

1 gün içinde %bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% USD
(1 Yorum)