none
(Sumber: milist SQL Server) Select Count di T-SQL RRS feed

  • Pertanyaan

  • Dear Temen-temen,
    Kenalkan saya baru di milis ini dan juga baru belajar SQL server Database, saya ada pertanyaan siapa tahu temen-temen bisa membantu, 
    saya buat sql seperti ini :
    ----------------
    SELECT MATERIALMOVEMENTDATE, MATERIALMOVEMENTSHIFT, MATERIALSOURCE
         , MATERIALDESTINATION, MATERIALBENCH, TRUCKTYPE, MATERIALORECLASS,
         MIN(CASE WHEN NAME ='MaterialMovementAu' THEN VALUE ELSE NULL END) as [MaterialMovementAu],
         MIN(CASE WHEN NAME ='MaterialMovementAg' THEN VALUE ELSE NULL END) as [MaterialMovementAg],
         MIN(CASE WHEN NAME ='MaterialSG' THEN CAST(VALUE AS FLOAT) ELSE NULL END) as [MaterialSG],
         MIN(CASE WHEN NAME ='ProdOreBlockName' THEN VALUE ELSE NULL END) as [ProdOreBlockName],
         MIN(CASE WHEN NAME ='TruckFactor' THEN CAST(VALUE AS FLOAT) ELSE NULL END) as [TruckFactor],
         MIN(CASE WHEN NAME ='TruckTally' THEN CAST(VALUE AS FLOAT) ELSE NULL END) as [TruckTally]
    FROM AT_MATERIALMOVEMENTSDETAILS
    WHERE AT_MATERIALMOVEMENTSDETAILS.NAME IN ('MaterialMovementAu','MaterialMovementAg',
                                            'MaterialSG','ProdOreBlockName','TruckFactor','TruckTally')
    GROUP BY MATERIALMOVEMENTDATE, MATERIALMOVEMENTSHIFT, MATERIALSOURCE , MATERIALDESTINATION, MATERIALBENCH, TRUCKTYPE, MATERIALORECLASS

    ---------------
    Sudah berhasil tapi dari hasil ini saya mau query lagi menjadi
    SELECT COUNT berdasarkan MaterialSG nya bisakah digabung atau ada cara lain.




    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Senin, 30 April 2012 08.14
    Moderator

Jawaban

  • pake sub query dulu saja
    SELEC ...
    FROM
        (SELECT FROM ...)
    GROUP BY
    tapi ingat, kalo sudah mahir sub query jangan digunakan terlalu 'dalam' sub query itu jelek dari segi performance.

    Dijawab oleh: Didit Banuardi


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Senin, 30 April 2012 08.15
    Moderator

Semua Balasan

  • pake sub query dulu saja
    SELEC ...
    FROM
        (SELECT FROM ...)
    GROUP BY
    tapi ingat, kalo sudah mahir sub query jangan digunakan terlalu 'dalam' sub query itu jelek dari segi performance.

    Dijawab oleh: Didit Banuardi


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Senin, 30 April 2012 08.15
    Moderator
  • boleh ikut bantuin ya...
    CTE = common table expression  ini seperti view , tapi tidak disimpan secara permanen  dalam DBMS

    sintaksnya :

    With CTE_table( field1,field2,....,fieldn)
    as
    (
    select
    min(), min(), min(),.......,
    from
    where
    group by ................. 
    )
    select count(  )from CTE_table


    kalau pake subquery: juga bisa, cuma kelihatannya nggak usah pake group by....

    SELECT COUNT(MaterialSG)
    FROM (SUB QUERY)
    GROUP BY MaterialSG

    Dijawab oleh: Mira


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Senin, 30 April 2012 08.24
    Moderator