CODEWITHSUNDEEP
sqlsql-order-byunion
Michael
Michael

Reputation: 21

SQL: Using UNION

Here is the question and database info.

Use the UNION command to prepare a full statement for customer 'C001' - it should be laid out as follows. (Note that the values shown below are not correct.) You may be able to use '' or NULL for blank values - if necessary use 0. enter image description here

Here is a link to the webpage with the database info. http://sqlzoo.net/5_0.htm or see the image below. enter image description here

Here is what I have tried:

    SELECT sdate AS LineDate, "delivery" AS LEGEND, price*quantity AS Total,"" AS Amount
    FROM shipped
    JOIN product ON (shipped.product=product.id)
    WHERE badguy='C001'
    UNION
    SELECT rdate,notes, "",receipt.amount
    FROM receipt
    WHERE badguy='C001'

Here is what I get back:

Wrong Answer. The correct answer has 5 row(s).

enter image description here

The amounts don't seem right in the amount column and I can't figure out how to order the data by the date since it is using two different date columns (sdate and rdate which are UNIONED).

Upvotes: 0

Views: 137

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95572

It's usually easiest to develop each part of the union separately. Pay attention to the use of "null" to separate the monetary columns. The first select gets to name the columns.

select s.sdate as tr_date, 'Delivery' as type, sum((s.quantity * p.price)) as extended_price, null as amount
  from shipped s
  inner join product p on p.id = s.product
  where badguy = 'C001'
  group by s.sdate
union all
select rdate, notes, null, sum(amount)
  from receipt
  where badguy = 'C001'
  group by rdate, notes
order by tr_date

Upvotes: 0

Diego
Diego

Reputation: 18359

Looks like the data in the example is being aggregated by date and charge type using group by, that's why you are getting too many rows. Also, you can sort by the alias of the column (LineDate) and the order by clause will apply to all the rows in the union.

  SELECT sdate AS LineDate, "delivery" AS LEGEND, SUM(price*quantity) AS Total,"" AS Amount
    FROM shipped
    JOIN product ON (shipped.product=product.id)
    WHERE badguy='C001'
    GROUP BY sdate
    UNION
    SELECT rdate, notes, "",receipt.amount
    FROM receipt
    WHERE badguy='C001'
  ORDER BY LineDate

Upvotes: 1

Related Questions