SQL:求和返回结果的最大值 [英] SQL: SUM the MAX values of results returned
问题描述
以下 SQL 语句产生下面列出的结果.问题是，如何修改此语句以仅提供单个值的总和，即 6,940?
The following SQL statement produces the results listed below. Question is, how do I modify this statement to provide just the total sum of individual values which is 6,940?
SELECT
post_metrics.post_id,
post_metric_types.name,
MAX(post_metrics.value)
FROM post_metrics
INNER JOIN post_metric_types ON post_metric_types.id = post_metrics.post_metric_type_id
LEFT JOIN posts ON posts.id = post_metrics.post_id
WHERE post_metrics.post_metric_type_id = 2
AND posts.channel_id = 2268
GROUP BY post_metrics.post_id
ORDER BY post_metrics.post_metric_type_id, post_metrics.post_id, post_metrics.value DESC
POSTID:值；
1:25;2:588；3:12；4:0；5:74；6:12；7:4；8:27；9:13；10:70；11:0；12:28；13:0；14:204；15:181；16:101；17:17；18:19；19:0；20:171；21:37；22:72；23:25；24:82；25:81；26:164；27:0；28:37；29:215；30:6；31:0；32:203；33:10；34:7；35:29；36:71；37:39；38:8；39:0；40:28；41:4；42:35；43:44；44:9；45:0；46:18；47:125；48:6；49:30；50:0；51:6；52:11；53:8；54:74；55:0；56:10；57:8；58:11；59:0；60:12；61:15；62:0；63:108；64:9；65:72；66:24；67:0；68:19；69:86；70:0；71:16；72:25；73:10；74:23；75:12；76:23；77:10；78:0；79:0；80:101；81:141；82:14；83:0；84:5；85:43；86:36；87:0；88:9；89:22；90:14；91:10；92:17；93:6；94:3；95:0；96:9；97:9；98:0；99:67；100:18；101:10；102:3；103:0；104:7；105:0；106:9；107:13；108:3；109:10；110:27；111:14；112:60；113:22；114:11；115:10；116:0；117:15；118:11；119:20；120:0；121:42；122:26；123:0；124:15；125:30；126:4；127:66；128:9；129:35；130:5；131:0；132:17；133:0；134:616；135:5；136:6；137:0；138:4；139:24；140:5；141:0；142:5；143:710；144:0；145:30；146:6；147:25；148:4；149:7；150:6；151:0；152:28；153:6；154:9；155:0；156:10；157:4；158:2；159:0；160:0；161:51；162:10；163:6；164:35；165:6；166:2；167:0；168:0；169:13；170:5；171:4；172:3；173:9；174:8；175:3；176:0；177:19；178:45；179:0；180:27；181:0；182:13；183:8；184:9；185:5；186:0；187:103；188:4；189:12；190:5；191:10；192:8；193:5；194:6；195:6；196:6；197:6；198:87；199:4；200:55；201:30；202:9；203:32；204:9；205:0；206:10；207:0；208:29；209:11；210:10；211:4；212:0；213:44；214:101；
1: 25; 2: 588; 3: 12; 4: 0; 5: 74; 6: 12; 7: 4; 8: 27; 9: 13; 10: 70; 11: 0; 12: 28; 13: 0; 14: 204; 15: 181; 16: 101; 17: 17; 18: 19; 19: 0; 20: 171; 21: 37; 22: 72; 23: 25; 24: 82; 25: 81; 26: 164; 27: 0; 28: 37; 29: 215; 30: 6; 31: 0; 32: 203; 33: 10; 34: 7; 35: 29; 36: 71; 37: 39; 38: 8; 39: 0; 40: 28; 41: 4; 42: 35; 43: 44; 44: 9; 45: 0; 46: 18; 47: 125; 48: 6; 49: 30; 50: 0; 51: 6; 52: 11; 53: 8; 54: 74; 55: 0; 56: 10; 57: 8; 58: 11; 59: 0; 60: 12; 61: 15; 62: 0; 63: 108; 64: 9; 65: 72; 66: 24; 67: 0; 68: 19; 69: 86; 70: 0; 71: 16; 72: 25; 73: 10; 74: 23; 75: 12; 76: 23; 77: 10; 78: 0; 79: 0; 80: 101; 81: 141; 82: 14; 83: 0; 84: 5; 85: 43; 86: 36; 87: 0; 88: 9; 89: 22; 90: 14; 91: 10; 92: 17; 93: 6; 94: 3; 95: 0; 96: 9; 97: 9; 98: 0; 99: 67; 100: 18; 101: 10; 102: 3; 103: 0; 104: 7; 105: 0; 106: 9; 107: 13; 108: 3; 109: 10; 110: 27; 111: 14; 112: 60; 113: 22; 114: 11; 115: 10; 116: 0; 117: 15; 118: 11; 119: 20; 120: 0; 121: 42; 122: 26; 123: 0; 124: 15; 125: 30; 126: 4; 127: 66; 128: 9; 129: 35; 130: 5; 131: 0; 132: 17; 133: 0; 134: 616; 135: 5; 136: 6; 137: 0; 138: 4; 139: 24; 140: 5; 141: 0; 142: 5; 143: 710; 144: 0; 145: 30; 146: 6; 147: 25; 148: 4; 149: 7; 150: 6; 151: 0; 152: 28; 153: 6; 154: 9; 155: 0; 156: 10; 157: 4; 158: 2; 159: 0; 160: 0; 161: 51; 162: 10; 163: 6; 164: 35; 165: 6; 166: 2; 167: 0; 168: 0; 169: 13; 170: 5; 171: 4; 172: 3; 173: 9; 174: 8; 175: 3; 176: 0; 177: 19; 178: 45; 179: 0; 180: 27; 181: 0; 182: 13; 183: 8; 184: 9; 185: 5; 186: 0; 187: 103; 188: 4; 189: 12; 190: 5; 191: 10; 192: 8; 193: 5; 194: 6; 195: 6; 196: 6; 197: 6; 198: 87; 199: 4; 200: 55; 201: 30; 202: 9; 203: 32; 204: 9; 205: 0; 206: 10; 207: 0; 208: 29; 209: 11; 210: 10; 211: 4; 212: 0; 213: 44; 214: 101;
= 6,904
谢谢
推荐答案
将现有查询用作子查询是一种方法:
Using your existing query as a subquery would be one way:
SELECT SUM(maxpostmetric)
FROM (
SELECT
post_metrics.post_id,
post_metric_types.name,
MAX(post_metrics.value) maxpostmetric
FROM post_metrics
INNER JOIN post_metric_types ON post_metric_types.id = post_metrics.post_metric_type_id
LEFT JOIN posts ON posts.id = post_metrics.post_id
WHERE post_metrics.post_metric_type_id = 2
AND posts.channel_id = 2268
GROUP BY post_metrics.post_id
) t
祝你好运.
顺便说一句——您在您的帖子表上使用 LEFT JOIN，但随后在您的 WHERE 条件中包含该表中的一列.你可以把它变成一个INNER JOIN.
BTW -- You're using a LEFT JOIN on your posts table, but then including a column from that table in your WHERE criteria. You could just turn that into an INNER JOIN.
这篇关于SQL:求和返回结果的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
