英文:
How to merge two sql queries results? One query has GROUP BY
问题
我有一个如下所示的表格
id, date, type, quantity, vendor
'1','2020-04-05','2424A','200','vendor1'
'2','2020-04-05','2424','350','vendor1'
'3','2020-04-05','2424A1','150','vendor1'
'4','2020-04-05','2425','400','vendor1'
'5','2020-04-05','MA5878','200','vendor2'
我在后端使用Java。我已经尝试了以下查询：
SELECT vendor, type, quantity FROM reports;
vendor, type, quantity
'vendor1', '2511', '200'
'vendor1', '5120', '350'
'vendor1', '2520', '150'
'vendor1', '5114', '400'
SELECT vendor, SUM(quantity) FROM reports where date = '2020-04-05' GROUP BY vendor;
vendor, SUM(quantity)
'vendor1', '1100'
'vendor2', '20600'
我需要将上述两个查询的结果合并。有人可以指导我如何进一步操作。
英文:
I have a table like below
id, date, type, quantity, vendor
'1','2020-04-05','2424A','200','vendor1'
'2','2020-04-05','2424','350','vendor1'
'3','2020-04-05','2424A1','150','vendor1'
'4','2020-04-05','2425','400','vendor1'
'5','2020-04-05','MA5878','200','vendor2'
I am using Java as my backend.
I have tried these queries
SELECT vendor, type, quantity
FROM reports;
vendor, type, quantity
'vendor1', '2511', '200'
'vendor1', '5120', '350'
'vendor1', '2520', '150'
'vendor1', '5114', '400'
SELECT vendor, SUM(quantity)
FROM reports
where date = '2020-04-05'
GROUP BY vendor;
vendor, SUM(quantity)
'vendor1', '1100'
'vendor2', '20600'
I need to combine the above 2 queries results. Can someone guide me to proceed further.
答案1
得分: 1
尝试一下这个：
WITH a AS (
SELECT vendor, ont_type, quantity
FROM cpe_portal.ontfereports
), b as (
SELECT vendor, SUM(quantity) as quantityb
FROM cpe_portal.ontfereports
WHERE date = '2020-04-05'
GROUP BY vendor
)
SELECT a.vendor, a.ont_type, a.quantity, b.quantityb
FROM a join b ON a.vendor = b.vendor
英文:
Give this a try:
WITH a AS (
SELECT vendor, ont_type, quantity
FROM cpe_portal.ontfereports
), b as (
SELECT vendor, SUM(quantity) as quantityb
FROM cpe_portal.ontfereports
WHERE date = '2020-04-05'
GROUP BY vendor
)
SELECT a.vendor, a.ont_type, a.quantity, b.quantityb
FROM a join b ON a.vendor = b.vendor
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论