sql group by - curse and blessing
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
1
Hey I have 3 tables for a meal calculation.
CREATE TABLE Meals (
meal_id int,
name text
);
CREATE TABLE Ingredients (
ingredient_id int,
name text,
minamount float,
price float
);
CREATE TABLE Recipe (
meal_id int,
ingredient_id int,
quantitiy float,
ingredientName text
);
Now I like to find a meal by ingredient:
SELECT m.meal_id,
m.name,
r.ingredientName
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
group by m.meal_id
Which give me a list like:
name, ingredientName
Lasagne, meat
Bolognese, meat
Also I like to find meal by price like:
SELECT m.meal_id,
m.name,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
GROUP BY m.meal_id,
m.name
HAVING mealPrice > 5.0 AND mealPrice < 8.0
Which give me a list like:
name, mealPrice
Lasagne, 6.5
Bolognese, 7.8
Now let me build a query like
SELECT m.meal_id,
m.name,
r.ingredientName,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
GROUP BY m.meal_id
r.ingredientName
HAVING mealPrice > 5.0 AND mealPrice < 8.0
Wich give me a list like:
name, ingredientName, mealPrice
Lasagne, meat, 1.99
Bolognese, meat, 1.99
Because (i.price / i.minamount * r.quanitity) for this meal for meat is 1.99
Without "GROUP BY r.ingredientName" it returns only one values
name, ingredientName, mealPrice
Lasagne, meat, 6.5
But I need a list like:
name, ingredientName, mealPrice
Lasagne, meat, 6.5
Bolognese, meat, 7.8
I am really really new to SQL(need this query in my API for an Android Project..), and I don't know how to build a query for that, do I need something like a variable or a subquery I dont know....
mysql sql mysqli
edited Nov 22 '18 at 12:34
LukStorms
14.2k31734
asked Nov 22 '18 at 11:35
TomimotmotTomimotmot
157
|
show 1 more comment
1
Hey I have 3 tables for a meal calculation.
CREATE TABLE Meals (
meal_id int,
name text
);
CREATE TABLE Ingredients (
ingredient_id int,
name text,
minamount float,
price float
);
CREATE TABLE Recipe (
meal_id int,
ingredient_id int,
quantitiy float,
ingredientName text
);
Now I like to find a meal by ingredient:
SELECT m.meal_id,
m.name,
r.ingredientName
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
group by m.meal_id
Which give me a list like:
name, ingredientName
Lasagne, meat
Bolognese, meat
Also I like to find meal by price like:
SELECT m.meal_id,
m.name,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
GROUP BY m.meal_id,
m.name
HAVING mealPrice > 5.0 AND mealPrice < 8.0
Which give me a list like:
name, mealPrice
Lasagne, 6.5
Bolognese, 7.8
Now let me build a query like
SELECT m.meal_id,
m.name,
r.ingredientName,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
GROUP BY m.meal_id
r.ingredientName
HAVING mealPrice > 5.0 AND mealPrice < 8.0
Wich give me a list like:
name, ingredientName, mealPrice
Lasagne, meat, 1.99
Bolognese, meat, 1.99
Because (i.price / i.minamount * r.quanitity) for this meal for meat is 1.99
Without "GROUP BY r.ingredientName" it returns only one values
name, ingredientName, mealPrice
Lasagne, meat, 6.5
But I need a list like:
name, ingredientName, mealPrice
Lasagne, meat, 6.5
Bolognese, meat, 7.8
I am really really new to SQL(need this query in my API for an Android Project..), and I don't know how to build a query for that, do I need something like a variable or a subquery I dont know....
mysql sql mysqli
edited Nov 22 '18 at 12:34
LukStorms
14.2k31734
asked Nov 22 '18 at 11:35
TomimotmotTomimotmot
157
You could take a little time to fix the create tables which are syntactically incorrect and your queries r.showname for example does not exist.
– P.Salmon
Nov 22 '18 at 11:45
thanks a lot Salmon, r.showname is off course r.ingredientName, create table statement are simplefied for this post.. but I think the problem is clear, how do you solve it?
– Tomimotmot
Nov 22 '18 at 11:51
The HAVING clause is intended for aggregate function conditions.
– jarlh
Nov 22 '18 at 11:55
first mover.ingredientName = 'meat'toWHEREOR better toJOIN
– bato3
Nov 22 '18 at 11:59
thanks a lot @bato3, I changed it - but How can I get my expected value
– Tomimotmot
Nov 22 '18 at 12:07
|
show 1 more comment
1
1
1
Hey I have 3 tables for a meal calculation.
CREATE TABLE Meals (
meal_id int,
name text
);
CREATE TABLE Ingredients (
ingredient_id int,
name text,
minamount float,
price float
);
CREATE TABLE Recipe (
meal_id int,
ingredient_id int,
quantitiy float,
ingredientName text
);
Now I like to find a meal by ingredient:
SELECT m.meal_id,
m.name,
r.ingredientName
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
group by m.meal_id
Which give me a list like:
name, ingredientName
Lasagne, meat
Bolognese, meat
Also I like to find meal by price like:
SELECT m.meal_id,
m.name,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
GROUP BY m.meal_id,
m.name
HAVING mealPrice > 5.0 AND mealPrice < 8.0
Which give me a list like:
name, mealPrice
Lasagne, 6.5
Bolognese, 7.8
Now let me build a query like
SELECT m.meal_id,
m.name,
r.ingredientName,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
GROUP BY m.meal_id
r.ingredientName
HAVING mealPrice > 5.0 AND mealPrice < 8.0
Wich give me a list like:
name, ingredientName, mealPrice
Lasagne, meat, 1.99
Bolognese, meat, 1.99
Because (i.price / i.minamount * r.quanitity) for this meal for meat is 1.99
Without "GROUP BY r.ingredientName" it returns only one values
name, ingredientName, mealPrice
Lasagne, meat, 6.5
But I need a list like:
name, ingredientName, mealPrice
Lasagne, meat, 6.5
Bolognese, meat, 7.8
I am really really new to SQL(need this query in my API for an Android Project..), and I don't know how to build a query for that, do I need something like a variable or a subquery I dont know....
mysql sql mysqli
edited Nov 22 '18 at 12:34
LukStorms
14.2k31734
asked Nov 22 '18 at 11:35
TomimotmotTomimotmot
157
Hey I have 3 tables for a meal calculation.
CREATE TABLE Meals (
meal_id int,
name text
);
CREATE TABLE Ingredients (
ingredient_id int,
name text,
minamount float,
price float
);
CREATE TABLE Recipe (
meal_id int,
ingredient_id int,
quantitiy float,
ingredientName text
);
Now I like to find a meal by ingredient:
SELECT m.meal_id,
m.name,
r.ingredientName
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
group by m.meal_id
Which give me a list like:
name, ingredientName
Lasagne, meat
Bolognese, meat
Also I like to find meal by price like:
SELECT m.meal_id,
m.name,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
GROUP BY m.meal_id,
m.name
HAVING mealPrice > 5.0 AND mealPrice < 8.0
Which give me a list like:
name, mealPrice
Lasagne, 6.5
Bolognese, 7.8
Now let me build a query like
SELECT m.meal_id,
m.name,
r.ingredientName,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
GROUP BY m.meal_id
r.ingredientName
HAVING mealPrice > 5.0 AND mealPrice < 8.0
Wich give me a list like:
name, ingredientName, mealPrice
Lasagne, meat, 1.99
Bolognese, meat, 1.99
Because (i.price / i.minamount * r.quanitity) for this meal for meat is 1.99
Without "GROUP BY r.ingredientName" it returns only one values
name, ingredientName, mealPrice
Lasagne, meat, 6.5
But I need a list like:
name, ingredientName, mealPrice
Lasagne, meat, 6.5
Bolognese, meat, 7.8
I am really really new to SQL(need this query in my API for an Android Project..), and I don't know how to build a query for that, do I need something like a variable or a subquery I dont know....
mysql sql mysqli
mysql sql mysqli
edited Nov 22 '18 at 12:34
LukStorms
14.2k31734
asked Nov 22 '18 at 11:35
TomimotmotTomimotmot
157
edited Nov 22 '18 at 12:34
LukStorms
14.2k31734
asked Nov 22 '18 at 11:35
TomimotmotTomimotmot
157
edited Nov 22 '18 at 12:34
LukStorms
14.2k31734
edited Nov 22 '18 at 12:34
LukStorms
14.2k31734
edited Nov 22 '18 at 12:34
LukStorms
14.2k31734
14.2k31734
asked Nov 22 '18 at 11:35
TomimotmotTomimotmot
157
asked Nov 22 '18 at 11:35
TomimotmotTomimotmot
157
asked Nov 22 '18 at 11:35
TomimotmotTomimotmot
157
157
You could take a little time to fix the create tables which are syntactically incorrect and your queries r.showname for example does not exist.
– P.Salmon
Nov 22 '18 at 11:45
thanks a lot Salmon, r.showname is off course r.ingredientName, create table statement are simplefied for this post.. but I think the problem is clear, how do you solve it?
– Tomimotmot
Nov 22 '18 at 11:51
The HAVING clause is intended for aggregate function conditions.
– jarlh
Nov 22 '18 at 11:55
first mover.ingredientName = 'meat'toWHEREOR better toJOIN
– bato3
Nov 22 '18 at 11:59
thanks a lot @bato3, I changed it - but How can I get my expected value
– Tomimotmot
Nov 22 '18 at 12:07
|
show 1 more comment
You could take a little time to fix the create tables which are syntactically incorrect and your queries r.showname for example does not exist.
– P.Salmon
Nov 22 '18 at 11:45
thanks a lot Salmon, r.showname is off course r.ingredientName, create table statement are simplefied for this post.. but I think the problem is clear, how do you solve it?
– Tomimotmot
Nov 22 '18 at 11:51
The HAVING clause is intended for aggregate function conditions.
– jarlh
Nov 22 '18 at 11:55
first mover.ingredientName = 'meat'toWHEREOR better toJOIN
– bato3
Nov 22 '18 at 11:59
thanks a lot @bato3, I changed it - but How can I get my expected value
– Tomimotmot
Nov 22 '18 at 12:07
You could take a little time to fix the create tables which are syntactically incorrect and your queries r.showname for example does not exist.
– P.Salmon
Nov 22 '18 at 11:45
You could take a little time to fix the create tables which are syntactically incorrect and your queries r.showname for example does not exist.
– P.Salmon
Nov 22 '18 at 11:45
thanks a lot Salmon, r.showname is off course r.ingredientName, create table statement are simplefied for this post.. but I think the problem is clear, how do you solve it?
– Tomimotmot
Nov 22 '18 at 11:51
thanks a lot Salmon, r.showname is off course r.ingredientName, create table statement are simplefied for this post.. but I think the problem is clear, how do you solve it?
– Tomimotmot
Nov 22 '18 at 11:51
The HAVING clause is intended for aggregate function conditions.
– jarlh
Nov 22 '18 at 11:55
The HAVING clause is intended for aggregate function conditions.
– jarlh
Nov 22 '18 at 11:55
first move
r.ingredientName = 'meat' to WHERE OR better to JOIN– bato3
Nov 22 '18 at 11:59
first move
r.ingredientName = 'meat' to WHERE OR better to JOIN– bato3
Nov 22 '18 at 11:59
thanks a lot @bato3, I changed it - but How can I get my expected value
– Tomimotmot
Nov 22 '18 at 12:07
thanks a lot @bato3, I changed it - but How can I get my expected value
– Tomimotmot
Nov 22 '18 at 12:07
|
show 1 more comment
2 Answers
2
active
oldest
votes
0
Perhaps add an EXISTS to only get the meals with 'meat' in the recipe.
SELECT
m.meal_id, m.name,
SUM((i.price / i.minamount) * r.quantity) as mealPrice
FROM meals m
JOIN recipe r ON r.meal_id = m.meal_id
JOIN ingredients i ON i.ingredient_id = r.ingredient_id
WHERE EXISTS
(
SELECT 1
FROM recipe r2
WHERE r2.ingredientName = 'meat'
AND r2.meal_id = m.meal_id
)
GROUP BY m.meal_id, m.name
HAVING mealPrice BETWEEN 5.01 AND 7.99
answered Nov 22 '18 at 12:13
LukStormsLukStorms
14.2k31734
oh LukStorms thanks a lot! It works :)
– Tomimotmot
Nov 22 '18 at 12:17
1
:) Btw, if you want to verify if the ingredients of the meal contain meat? Then if your MySql version has the GROUP_CONCAT function , then you could play with that.
– LukStorms
Nov 22 '18 at 12:30
1
Also, I used a BETWEEN. That's just an alternative to themealPrice > 5.0 AND mealPrice < 8.0. So it's perfectly fine to use your method of limiting on the meatPrice. But note that a BETWEEN includes the start and the end value. Sowhere x between 1 and 3is equivalent towhere x >=1 and x <= 3
– LukStorms
Nov 22 '18 at 12:38
add a comment |
0
Try this
SELECT m.meal_id,
m.name,
r.ingredientName,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
GROUP BY m.meal_id, r.ingredientName
HAVING mealPrice > 5.0 AND mealPrice < 8.0
answered Nov 22 '18 at 12:06
Prashant Deshmukh.....Prashant Deshmukh.....
68658
I already tried it because bato3 said I should mover.ingredientName = 'meat'toWHERE
– Tomimotmot
Nov 22 '18 at 12:13
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53430094%2fsql-group-by-curse-and-blessing%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Perhaps add an EXISTS to only get the meals with 'meat' in the recipe.
SELECT
m.meal_id, m.name,
SUM((i.price / i.minamount) * r.quantity) as mealPrice
FROM meals m
JOIN recipe r ON r.meal_id = m.meal_id
JOIN ingredients i ON i.ingredient_id = r.ingredient_id
WHERE EXISTS
(
SELECT 1
FROM recipe r2
WHERE r2.ingredientName = 'meat'
AND r2.meal_id = m.meal_id
)
GROUP BY m.meal_id, m.name
HAVING mealPrice BETWEEN 5.01 AND 7.99
answered Nov 22 '18 at 12:13
LukStormsLukStorms
14.2k31734
oh LukStorms thanks a lot! It works :)
– Tomimotmot
Nov 22 '18 at 12:17
1
:) Btw, if you want to verify if the ingredients of the meal contain meat? Then if your MySql version has the GROUP_CONCAT function , then you could play with that.
– LukStorms
Nov 22 '18 at 12:30
1
Also, I used a BETWEEN. That's just an alternative to themealPrice > 5.0 AND mealPrice < 8.0. So it's perfectly fine to use your method of limiting on the meatPrice. But note that a BETWEEN includes the start and the end value. Sowhere x between 1 and 3is equivalent towhere x >=1 and x <= 3
– LukStorms
Nov 22 '18 at 12:38
add a comment |
0
Perhaps add an EXISTS to only get the meals with 'meat' in the recipe.
SELECT
m.meal_id, m.name,
SUM((i.price / i.minamount) * r.quantity) as mealPrice
FROM meals m
JOIN recipe r ON r.meal_id = m.meal_id
JOIN ingredients i ON i.ingredient_id = r.ingredient_id
WHERE EXISTS
(
SELECT 1
FROM recipe r2
WHERE r2.ingredientName = 'meat'
AND r2.meal_id = m.meal_id
)
GROUP BY m.meal_id, m.name
HAVING mealPrice BETWEEN 5.01 AND 7.99
answered Nov 22 '18 at 12:13
LukStormsLukStorms
14.2k31734
oh LukStorms thanks a lot! It works :)
– Tomimotmot
Nov 22 '18 at 12:17
1
:) Btw, if you want to verify if the ingredients of the meal contain meat? Then if your MySql version has the GROUP_CONCAT function , then you could play with that.
– LukStorms
Nov 22 '18 at 12:30
1
Also, I used a BETWEEN. That's just an alternative to themealPrice > 5.0 AND mealPrice < 8.0. So it's perfectly fine to use your method of limiting on the meatPrice. But note that a BETWEEN includes the start and the end value. Sowhere x between 1 and 3is equivalent towhere x >=1 and x <= 3
– LukStorms
Nov 22 '18 at 12:38
add a comment |
0
0
0
Perhaps add an EXISTS to only get the meals with 'meat' in the recipe.
SELECT
m.meal_id, m.name,
SUM((i.price / i.minamount) * r.quantity) as mealPrice
FROM meals m
JOIN recipe r ON r.meal_id = m.meal_id
JOIN ingredients i ON i.ingredient_id = r.ingredient_id
WHERE EXISTS
(
SELECT 1
FROM recipe r2
WHERE r2.ingredientName = 'meat'
AND r2.meal_id = m.meal_id
)
GROUP BY m.meal_id, m.name
HAVING mealPrice BETWEEN 5.01 AND 7.99
answered Nov 22 '18 at 12:13
LukStormsLukStorms
14.2k31734
Perhaps add an EXISTS to only get the meals with 'meat' in the recipe.
SELECT
m.meal_id, m.name,
SUM((i.price / i.minamount) * r.quantity) as mealPrice
FROM meals m
JOIN recipe r ON r.meal_id = m.meal_id
JOIN ingredients i ON i.ingredient_id = r.ingredient_id
WHERE EXISTS
(
SELECT 1
FROM recipe r2
WHERE r2.ingredientName = 'meat'
AND r2.meal_id = m.meal_id
)
GROUP BY m.meal_id, m.name
HAVING mealPrice BETWEEN 5.01 AND 7.99
answered Nov 22 '18 at 12:13
LukStormsLukStorms
14.2k31734
answered Nov 22 '18 at 12:13
LukStormsLukStorms
14.2k31734
answered Nov 22 '18 at 12:13
LukStormsLukStorms
14.2k31734
answered Nov 22 '18 at 12:13
LukStormsLukStorms
14.2k31734
14.2k31734
oh LukStorms thanks a lot! It works :)
– Tomimotmot
Nov 22 '18 at 12:17
1
:) Btw, if you want to verify if the ingredients of the meal contain meat? Then if your MySql version has the GROUP_CONCAT function , then you could play with that.
– LukStorms
Nov 22 '18 at 12:30
1
Also, I used a BETWEEN. That's just an alternative to themealPrice > 5.0 AND mealPrice < 8.0. So it's perfectly fine to use your method of limiting on the meatPrice. But note that a BETWEEN includes the start and the end value. Sowhere x between 1 and 3is equivalent towhere x >=1 and x <= 3
– LukStorms
Nov 22 '18 at 12:38
add a comment |
oh LukStorms thanks a lot! It works :)
– Tomimotmot
Nov 22 '18 at 12:17
1
:) Btw, if you want to verify if the ingredients of the meal contain meat? Then if your MySql version has the GROUP_CONCAT function , then you could play with that.
– LukStorms
Nov 22 '18 at 12:30
1
Also, I used a BETWEEN. That's just an alternative to themealPrice > 5.0 AND mealPrice < 8.0. So it's perfectly fine to use your method of limiting on the meatPrice. But note that a BETWEEN includes the start and the end value. Sowhere x between 1 and 3is equivalent towhere x >=1 and x <= 3
– LukStorms
Nov 22 '18 at 12:38
oh LukStorms thanks a lot! It works :)
– Tomimotmot
Nov 22 '18 at 12:17
oh LukStorms thanks a lot! It works :)
– Tomimotmot
Nov 22 '18 at 12:17
1
1
:) Btw, if you want to verify if the ingredients of the meal contain meat? Then if your MySql version has the GROUP_CONCAT function , then you could play with that.
– LukStorms
Nov 22 '18 at 12:30
:) Btw, if you want to verify if the ingredients of the meal contain meat? Then if your MySql version has the GROUP_CONCAT function , then you could play with that.
– LukStorms
Nov 22 '18 at 12:30
1
1
Also, I used a BETWEEN. That's just an alternative to the
mealPrice > 5.0 AND mealPrice < 8.0. So it's perfectly fine to use your method of limiting on the meatPrice. But note that a BETWEEN includes the start and the end value. So where x between 1 and 3 is equivalent to where x >=1 and x <= 3– LukStorms
Nov 22 '18 at 12:38
Also, I used a BETWEEN. That's just an alternative to the
mealPrice > 5.0 AND mealPrice < 8.0. So it's perfectly fine to use your method of limiting on the meatPrice. But note that a BETWEEN includes the start and the end value. So where x between 1 and 3 is equivalent to where x >=1 and x <= 3– LukStorms
Nov 22 '18 at 12:38
add a comment |
0
Try this
SELECT m.meal_id,
m.name,
r.ingredientName,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
GROUP BY m.meal_id, r.ingredientName
HAVING mealPrice > 5.0 AND mealPrice < 8.0
answered Nov 22 '18 at 12:06
Prashant Deshmukh.....Prashant Deshmukh.....
68658
I already tried it because bato3 said I should mover.ingredientName = 'meat'toWHERE
– Tomimotmot
Nov 22 '18 at 12:13
add a comment |
0
Try this
SELECT m.meal_id,
m.name,
r.ingredientName,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
GROUP BY m.meal_id, r.ingredientName
HAVING mealPrice > 5.0 AND mealPrice < 8.0
answered Nov 22 '18 at 12:06
Prashant Deshmukh.....Prashant Deshmukh.....
68658
I already tried it because bato3 said I should mover.ingredientName = 'meat'toWHERE
– Tomimotmot
Nov 22 '18 at 12:13
add a comment |
0
0
0
Try this
SELECT m.meal_id,
m.name,
r.ingredientName,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
GROUP BY m.meal_id, r.ingredientName
HAVING mealPrice > 5.0 AND mealPrice < 8.0
answered Nov 22 '18 at 12:06
Prashant Deshmukh.....Prashant Deshmukh.....
68658
Try this
SELECT m.meal_id,
m.name,
r.ingredientName,
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
where r.ingredientName = 'meat'
GROUP BY m.meal_id, r.ingredientName
HAVING mealPrice > 5.0 AND mealPrice < 8.0
answered Nov 22 '18 at 12:06
Prashant Deshmukh.....Prashant Deshmukh.....
68658
answered Nov 22 '18 at 12:06
Prashant Deshmukh.....Prashant Deshmukh.....
68658
answered Nov 22 '18 at 12:06
Prashant Deshmukh.....Prashant Deshmukh.....
68658
answered Nov 22 '18 at 12:06
Prashant Deshmukh.....Prashant Deshmukh.....
68658
68658
I already tried it because bato3 said I should mover.ingredientName = 'meat'toWHERE
– Tomimotmot
Nov 22 '18 at 12:13
add a comment |
I already tried it because bato3 said I should mover.ingredientName = 'meat'toWHERE
– Tomimotmot
Nov 22 '18 at 12:13
I already tried it because bato3 said I should move
r.ingredientName = 'meat' to WHERE– Tomimotmot
Nov 22 '18 at 12:13
I already tried it because bato3 said I should move
r.ingredientName = 'meat' to WHERE– Tomimotmot
Nov 22 '18 at 12:13
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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.
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53430094%2fsql-group-by-curse-and-blessing%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
You could take a little time to fix the create tables which are syntactically incorrect and your queries r.showname for example does not exist.
– P.Salmon
Nov 22 '18 at 11:45
thanks a lot Salmon, r.showname is off course r.ingredientName, create table statement are simplefied for this post.. but I think the problem is clear, how do you solve it?
– Tomimotmot
Nov 22 '18 at 11:51
The HAVING clause is intended for aggregate function conditions.
– jarlh
Nov 22 '18 at 11:55
first move
r.ingredientName = 'meat'toWHEREOR better toJOIN– bato3
Nov 22 '18 at 11:59
thanks a lot @bato3, I changed it - but How can I get my expected value
– Tomimotmot
Nov 22 '18 at 12:07