Select distinct in temp table

Question

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

Is it MySql or Sql server? those are two different products. — Zohar Peled, May 19 '16 at 05:15
@shamimreza here's the main problem I have too long for typed in here http://stackoverflow.com/questions/37293592/how-to-sum-with-conditions-on-other-columns/37295702#37295702 — Gusti Aldi, May 19 '16 at 05:22

score 1 · Answer 1 · edited May 23 '17 at 11:59

If you really need it to be in a temporary table, another approach is using "SELECT INTO" wherein you wont need to declare the creation of a temporary table. (Although creating a table then inserting records is the more preferred method https://stackoverflow.com/a/6948850/6344844)

SELECT DISTINCT
      p.PONumber,
      p.POdate,
      p.customername,
      p.[description],
      SUM(q.deliveryqty)
INTO #TEMPTABLE_NAME
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]

SELECT * FROM #TEMPTABLE_NAME

DROP TABLE #TEMPTABLE_NAME

score 0 · Answer 2 · answered May 19 '16 at 05:15

You have to first create the table then insert into the table. For SQL server You can do like this.

 CREATE TABLE TEMPORARY(
 PONumber varchar(10),
 POdate varchar(10),
 customername varchar(35),
 description varchar(22)
 );

insert into TEMPORARY SELECT DISTINCT
PONumber varchar(10),
POdate varchar(10),
customername varchar(35),
description varchar(22)
FROM
 tb_po ;

If you want to create temp table in SQL server then you have use # before table name. For reference http://www.c-sharpcorner.com/uploadfile/b19d5a/temporary-and-global-temporary-table-in-sql-server-2008/

score 0 · Accepted Answer · answered May 19 '16 at 05:18

0

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]

answered May 19 '16 at 05:18

jelliaes

485
5
18

it works dude thx :) me just confuse before to make group by based on 2 column on different table :) – Gusti Aldi May 19 '16 at 06:49

Select distinct in temp table

3 Answers3