How to remove some data not needed on new row from mysql query with rollupindex doesn't work properlyMySQL -...
What does an unprocessed RAW file look like?
Players preemptively rolling, even though their rolls are useless or are checking the wrong skills
Including proofs of known theorems in master's thesis
Tikz: Perpendicular FROM a line
How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?
What's the reason that we have a different number of days each month?
Why "rm -r" is unable to delete this folder?
Intersection of 3 planes in 3D space
How can I put a period right after the algorithm's number in the algorithm's title?
Is the percentage symbol a constant?
How would an EMP effect spacesuits (and small-arms weapons)?
Boss asked me to sign a resignation paper without a date on it along with my new contract
What is an explicit bijection in combinatorics?
Promise.all returning empty objects
Is it possible to detect 100% of SQLi with a simple regex?
Was Opportunity's last message to Earth "My battery is low and it's getting dark"?
What can I do to encourage my players to use their consumables?
Why is it that Bernie Sanders is always called a "socialist"?
Buying a "Used" Router
Dealing with an internal ScriptKiddie
How to know if I am a 'Real Developer'
What is an efficient way to digitize a family photo collection?
How can I prevent an oracle who can see into the past from knowing everything that has happened?
Is layered encryption more secure than long passwords?
How to remove some data not needed on new row from mysql query with rollup
index doesn't work properlyMySQL - Group By - How does it determine which row to return from joined tableselecting complete rows grouped by one column based on the maximum of anotherMySQL join on hamming distanceIs it possible to clean up duplicate entries with just SQL?How can I improve my product facet search database model (EAV)how to optimize mysql query/ structure/ approach for large datasets?MySQL get previous row with inner joinMysql query - updating each record in table with sum, count of previous recordsgreatest n per group with a 3 tables join
Hello I have a problem with the rollup mysql query. I want to get total of each column. I use mysql SUM() and ROLLUP to get the result. But on the new row that is added it copies the data of the last row. How can I remove those data. I provided an image on what I really needed to achieve.
Here is my query.
SELECT IFNULL(payments.id, "General Total"), payments.driver_id,
payments.vehicle_specifications_id,payments.admins_id,
payments.vehicle_id, payments.boundaries, payments.cashbond_payments,
payments.loans, payments.penalties, SUM(payments.total_payments),
a.first_name, a.mid_name, a.last_name, d2.date, d.first_name,
d.mid_name, d.last_name, v.plate_number, v2.car_rate
FROM payments
INNER JOIN admins AS a ON payments.admins_id = a.id
INNER JOIN drivers AS d ON payments.driver_id = d.id
INNER JOIN dispatch AS d2 ON payments.dispatch_id =d2.id
INNER JOIN vehicles AS v ON payments.vehicle_id = v.id
INNER JOIN vehicle_specifications AS v2
ON payments.vehicle_specifications_id = v2.id
GROUP BY payments.id WITH ROLLUP
mysql group-by
edited May 11 '15 at 16:14
Rick James
42.9k22259
asked May 11 '15 at 11:29
Yves GonzagaYves Gonzaga
1063
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Hello I have a problem with the rollup mysql query. I want to get total of each column. I use mysql SUM() and ROLLUP to get the result. But on the new row that is added it copies the data of the last row. How can I remove those data. I provided an image on what I really needed to achieve.
Here is my query.
SELECT IFNULL(payments.id, "General Total"), payments.driver_id,
payments.vehicle_specifications_id,payments.admins_id,
payments.vehicle_id, payments.boundaries, payments.cashbond_payments,
payments.loans, payments.penalties, SUM(payments.total_payments),
a.first_name, a.mid_name, a.last_name, d2.date, d.first_name,
d.mid_name, d.last_name, v.plate_number, v2.car_rate
FROM payments
INNER JOIN admins AS a ON payments.admins_id = a.id
INNER JOIN drivers AS d ON payments.driver_id = d.id
INNER JOIN dispatch AS d2 ON payments.dispatch_id =d2.id
INNER JOIN vehicles AS v ON payments.vehicle_id = v.id
INNER JOIN vehicle_specifications AS v2
ON payments.vehicle_specifications_id = v2.id
GROUP BY payments.id WITH ROLLUP
mysql group-by
edited May 11 '15 at 16:14
Rick James
42.9k22259
asked May 11 '15 at 11:29
Yves GonzagaYves Gonzaga
1063
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Hello I have a problem with the rollup mysql query. I want to get total of each column. I use mysql SUM() and ROLLUP to get the result. But on the new row that is added it copies the data of the last row. How can I remove those data. I provided an image on what I really needed to achieve.
Here is my query.
SELECT IFNULL(payments.id, "General Total"), payments.driver_id,
payments.vehicle_specifications_id,payments.admins_id,
payments.vehicle_id, payments.boundaries, payments.cashbond_payments,
payments.loans, payments.penalties, SUM(payments.total_payments),
a.first_name, a.mid_name, a.last_name, d2.date, d.first_name,
d.mid_name, d.last_name, v.plate_number, v2.car_rate
FROM payments
INNER JOIN admins AS a ON payments.admins_id = a.id
INNER JOIN drivers AS d ON payments.driver_id = d.id
INNER JOIN dispatch AS d2 ON payments.dispatch_id =d2.id
INNER JOIN vehicles AS v ON payments.vehicle_id = v.id
INNER JOIN vehicle_specifications AS v2
ON payments.vehicle_specifications_id = v2.id
GROUP BY payments.id WITH ROLLUP
mysql group-by
edited May 11 '15 at 16:14
Rick James
42.9k22259
asked May 11 '15 at 11:29
Yves GonzagaYves Gonzaga
1063
Hello I have a problem with the rollup mysql query. I want to get total of each column. I use mysql SUM() and ROLLUP to get the result. But on the new row that is added it copies the data of the last row. How can I remove those data. I provided an image on what I really needed to achieve.
Here is my query.
SELECT IFNULL(payments.id, "General Total"), payments.driver_id,
payments.vehicle_specifications_id,payments.admins_id,
payments.vehicle_id, payments.boundaries, payments.cashbond_payments,
payments.loans, payments.penalties, SUM(payments.total_payments),
a.first_name, a.mid_name, a.last_name, d2.date, d.first_name,
d.mid_name, d.last_name, v.plate_number, v2.car_rate
FROM payments
INNER JOIN admins AS a ON payments.admins_id = a.id
INNER JOIN drivers AS d ON payments.driver_id = d.id
INNER JOIN dispatch AS d2 ON payments.dispatch_id =d2.id
INNER JOIN vehicles AS v ON payments.vehicle_id = v.id
INNER JOIN vehicle_specifications AS v2
ON payments.vehicle_specifications_id = v2.id
GROUP BY payments.id WITH ROLLUP
mysql group-by
mysql group-by
edited May 11 '15 at 16:14
Rick James
42.9k22259
asked May 11 '15 at 11:29
Yves GonzagaYves Gonzaga
1063
edited May 11 '15 at 16:14
Rick James
42.9k22259
asked May 11 '15 at 11:29
Yves GonzagaYves Gonzaga
1063
edited May 11 '15 at 16:14
Rick James
42.9k22259
edited May 11 '15 at 16:14
Rick James
42.9k22259
edited May 11 '15 at 16:14
Rick James
42.9k22259
42.9k22259
asked May 11 '15 at 11:29
Yves GonzagaYves Gonzaga
1063
asked May 11 '15 at 11:29
Yves GonzagaYves Gonzaga
1063
asked May 11 '15 at 11:29
Yves GonzagaYves Gonzaga
1063
1063
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Plan A (rather tedious):
IFNULL(payments.id, "General Total"),
IF(payments.id IS NULL, '', payments.driver_id), -- conditionally blank each
...
Plan B:
( SELECT ... ) -- without ROLLUP
UNION ALL
( SELECT 'Grand Total', '', ... SUM(...), '', ... ) -- Just total
ORDER BY ... -- to get "Grand Total" to be last
answered May 11 '15 at 16:20
Rick JamesRick James
42.9k22259
1
Thanks I already found the answer here stackoverflow.com/posts/comments/48443988
– Yves Gonzaga
May 11 '15 at 18:35
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f101154%2fhow-to-remove-some-data-not-needed-on-new-row-from-mysql-query-with-rollup%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Plan A (rather tedious):
IFNULL(payments.id, "General Total"),
IF(payments.id IS NULL, '', payments.driver_id), -- conditionally blank each
...
Plan B:
( SELECT ... ) -- without ROLLUP
UNION ALL
( SELECT 'Grand Total', '', ... SUM(...), '', ... ) -- Just total
ORDER BY ... -- to get "Grand Total" to be last
answered May 11 '15 at 16:20
Rick JamesRick James
42.9k22259
1
Thanks I already found the answer here stackoverflow.com/posts/comments/48443988
– Yves Gonzaga
May 11 '15 at 18:35
add a comment |
Plan A (rather tedious):
IFNULL(payments.id, "General Total"),
IF(payments.id IS NULL, '', payments.driver_id), -- conditionally blank each
...
Plan B:
( SELECT ... ) -- without ROLLUP
UNION ALL
( SELECT 'Grand Total', '', ... SUM(...), '', ... ) -- Just total
ORDER BY ... -- to get "Grand Total" to be last
answered May 11 '15 at 16:20
Rick JamesRick James
42.9k22259
1
Thanks I already found the answer here stackoverflow.com/posts/comments/48443988
– Yves Gonzaga
May 11 '15 at 18:35
add a comment |
Plan A (rather tedious):
IFNULL(payments.id, "General Total"),
IF(payments.id IS NULL, '', payments.driver_id), -- conditionally blank each
...
Plan B:
( SELECT ... ) -- without ROLLUP
UNION ALL
( SELECT 'Grand Total', '', ... SUM(...), '', ... ) -- Just total
ORDER BY ... -- to get "Grand Total" to be last
answered May 11 '15 at 16:20
Rick JamesRick James
42.9k22259
Plan A (rather tedious):
IFNULL(payments.id, "General Total"),
IF(payments.id IS NULL, '', payments.driver_id), -- conditionally blank each
...
Plan B:
( SELECT ... ) -- without ROLLUP
UNION ALL
( SELECT 'Grand Total', '', ... SUM(...), '', ... ) -- Just total
ORDER BY ... -- to get "Grand Total" to be last
answered May 11 '15 at 16:20
Rick JamesRick James
42.9k22259
answered May 11 '15 at 16:20
Rick JamesRick James
42.9k22259
answered May 11 '15 at 16:20
Rick JamesRick James
42.9k22259
answered May 11 '15 at 16:20
Rick JamesRick James
42.9k22259
42.9k22259
1
Thanks I already found the answer here stackoverflow.com/posts/comments/48443988
– Yves Gonzaga
May 11 '15 at 18:35
add a comment |
1
Thanks I already found the answer here stackoverflow.com/posts/comments/48443988
– Yves Gonzaga
May 11 '15 at 18:35
1
1
Thanks I already found the answer here stackoverflow.com/posts/comments/48443988
– Yves Gonzaga
May 11 '15 at 18:35
Thanks I already found the answer here stackoverflow.com/posts/comments/48443988
– Yves Gonzaga
May 11 '15 at 18:35
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f101154%2fhow-to-remove-some-data-not-needed-on-new-row-from-mysql-query-with-rollup%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
