Google BigQuery - Merge two Queries into one (sql)-CodePudding

In Google BigQuery using SQL, I have 2 separate queries that I want to merge into one. They are both extracting the same fields from the same tables but for different for time, items and location. I don't want any cross over of data from one query to the other but I want the output together.

Query 1

SELECT Revenue_Date, Item_Number ,Location_Number, Revenue_Group, Revenue_Value,COUNT (Ticket_Number)
FROM Revenue_Table
WHERE Revenue_Date ("201501","201502","201601",201602")
AND Item_Number ("2987","9876","2345")
AND Location_Number ("23456","23456","0987")
AND (Revenue_Group NOT IN ("123B","765C",345G","3456") or Revenue_Group is null)
GROUP BY Revenue_Date, Item_Number ,Location_Number,Revenue_Value ,Revenue_Group

Query 2

SELECT Revenue_Date, Item_Number ,Location_Number, Revenue_Group, Revenue_Value ,COUNT (Ticket_Number)
FROM Revenue_Table
WHERE Revenue_Date ("201502","201503","201602",201603")
AND Item_Number ("1678","2225","1098")
AND Location_Number ("09876","23456","2111")
AND (Revenue_Group NOT IN ("123B","765C",345G","3456") or Revenue_Group is null)
GROUP BY Revenue_Date, Item_Number ,Location_Number,Revenue_Value ,Revenue_Group

Following are the unique data for each query:

Date Query 1 Revenue_Date ("201501","201502","201601",201602")
Query 2 Revenue_Date ("201502","201503","201602",201603")

Item Query 1 Item_Number ("2987","9876","2345")
Query 2 Item_Number ("1678","2225","1098")

Location Query 1 Location_Number ("23456","23456","0987")
Query 2 Location_Number ("09876","23456","2111")

I want both queries to extract the data separately (i.e I don't want revenue for item 1678 in date 201501 (YYYYMM) or location 23456), once the data has been extracted I want it displayed in one table in the out put.

Can anyone help. Thanks.

CodePudding user response：

This does seem pretty straightforward forward or am I missing something? Have you tried using UNION ALL. This will merge both your result into one:

You need to make sure that both the queries separated by UNION or UNION ALL need to have the exact same number of columns.

SELECT 
Revenue_Date
,Item_Number 
,Location_Number
,Revenue_Group
,Revenue_Value
,COUNT(Ticket_Number)   
FROM  
Revenue_Table  
WHERE 
Revenue_Date ('201501','201502','201601','201602')  
AND Item_Number ('2987','9876','2345')  
AND Location_Number ('23456','23456','0987')  
AND (Revenue_Group NOT IN ('123B','765C','345G','3456') 
OR Revenue_Group IS NULL)  
GROUP BY 
Revenue_Date
,Item_Number 
,Location_Number
,Revenue_Value 
,Revenue_Group

UNION ALL

SELECT 
Revenue_Date
,Item_Number 
,Location_Number
,Revenue_Group
,Revenue_Value 
,COUNT(Ticket_Number)  
FROM  
Revenue_Table  
WHERE 
Revenue_Date ('201502','201503','201602','201603')  
AND Item_Number ('1678','2225','1098')  
AND Location_Number ('09876','23456','2111')  
AND (Revenue_Group NOT IN ('123B','765C','345G','3456') OR Revenue_Group IS NULL)  
GROUP BY 
Revenue_Date
,Item_Number 
,Location_Number
,Revenue_Value 
,Revenue_Group

CodePudding user response：

Or try it in one query like this:

SELECT Revenue_Date, Item_Number ,Location_Number, Revenue_Group, Revenue_Value,COUNT (Ticket_Number)
FROM Revenue_Table
WHERE
    (( Revenue_Date ("201501","201502","201601",201602")
      AND Item_Number ("2987","9876","2345")
      AND Location_Number ("23456","23456","0987")
    )
    OR
    (
     Revenue_Date ("201502","201503","201602",201603")
     AND Item_Number ("1678","2225","1098")
     AND Location_Number ("09876","23456","2111")
    ))
    AND (Revenue_Group NOT IN ("123B","765C",345G","3456") or Revenue_Group is null)
GROUP BY Revenue_Date, Item_Number ,Location_Number,Revenue_Value ,Revenue_Group;