Gusti Aldi Gusti Aldi - 6 months ago 27
MySQL Question

Select distinct in temp table

me trying to make distinct data in temporary table, trying to simple it with create table #tabletemp still got wrong, it says unrecognize data type near distinct and comma or closing bracket was expected near ponumber

here's the code :

CREATE TEMPORARY TABLE t1(
SELECT DISTINCT
PONumber varchar(10),
POdate varchar(10),
customername varchar(35),
description varchar(22)
FROM tb_po
);

SELECT
p.PONumber,
p.podate,
p.customername,
p.description,
(
SELECT SUM(q.deliveryqty)
FROM tb_spb q
WHERE p.PONumber = q.PONumber AND p.description = q.description
) AS Total
FROM t1 p

Answer

You don't need to create a temporary table to get the result that you want. Here is my revised query based on your query:

SELECT DISTINCT
  p.PONumber,
  p.POdate,
  p.customername,
  p.[description],
  SUM(q.deliveryqty)
FROM tb_po p
    INNER JOIN tb_spb q 
        ON p.PONumber = q.PONumber 
        AND p.description = q.description
GROUP BY p.PONumber,p.POdate,p.customername,p.[description]