Sql intersect not returning what I want

I am following above but cannot create view so I do 2 select statements.
The union works and I only change union to intersect but then no rows are returned.

intersect is defined as :

This means that identifying customers who are in the USA and have spent more than $90 can be done with the following query:

SELECT * from customer_usa
INTERSECT
SELECT * from customer_gt_90_dollars;

I know there are customers that satisfy this requirement as they are returned by union. Somehow they do not appear with INTERSECT please explain?

Union returns over 90$ or in usa and over 90 correctly

This I expected to return rows USA Frank 1040 and Michelle1040 but does not

this query works fine , but not when i replace union with intersect.

%%sql

SELECT
        c.country,
        c.first_name,
        sum(i.total)
    FROM 
        invoice i
    INNER JOIN 
            customer c 
        ON 
            i.customer_id = c.customer_id
    
    GROUP BY 1
        HAVING SUM(i.total) > 90

        
        
        union
        
SELECT
        c.country,
        c.first_name,
        sum(i.total)
    FROM 
        invoice i
    INNER JOIN 
            customer c 
        ON 
            i.customer_id = c.customer_id
         WHERE country = "USA"
    
    GROUP BY 1
        order by 2 desc

have you tried experimenting with group by clause here ?

If you haven’t been able to solve this and want to experiment, you can try these two steps:

  1. try both queries separately(individually) with GROUP BY 1, 2 and GROUP BY 2
  2. try intersecting both the queries with GROUP BY 2 (in both of them)

2 here represents the customer name column.

1 Like

Hi Rucha,

Yes I managed to solve after thinking about it 24 hours.

It was a problem with grouping on the wrong column.

Regards
JB

1 Like