# QUERY PROBLEM

Hi All,

I have query below and it does not work.

What's wrong with my code ?

Thank you.
``````SELECT A.EmpCode, SUM(A.QtyLsn) + CAST(SUM(A.QtyPcs) / 12 AS INTEGER) AS QtyLsn, SUM(CAST(A.QtyPcs % 12 AS INTEGER)) AS QtyPcs, SUM(B.Biaya1 * SUM(QtyLsn + CAST(QtyPcs / 12 AS INTEGER))) AS NilaiPekerjaanPerLsn, SUM(B.Biaya * CAST(QtyPcs % 12 AS INTEGER)) AS NilaiPekerjaanPerPcs  FROM  ( SELECT B.EmpCode , B.BarangCode , B.PekerjaanCode , SUM(B.QtyLsn) AS QtyLsn, SUM(B.QtyPcs) AS QtyPcs FROM THTRSSTOK2011 AS A LEFT JOIN TDTRSSTOK2011 AS B ON A.NoTransaksi = B.NoTransaksi WHERE TipeTransaksi = 'PRD' AND  CONVERT(Char(8), TglTransaksi, 112) BETWEEN '20110502' AND '20110515' GROUP BY B.EmpCode, B.BarangCode, B.PekerjaanCode  ) A  LEFT JOIN TDHPPPRODUK AS B ON A.BarangCode = B.ProdukCode AND A.PekerjaanCode = B.PekerjaanCode GROUP BY A.EmpCode
``````
Microsoft SQL Server 2005SQL

Last Comment
emi_sastra
s_chilkury

Do you get any specific error?
emi_sastra

Hi s_chilkury,

Allow me to describe what I am going to do.

The inside query is summing from detail data.
All dozens (QtyLsn) and pieces (QtyPcs).

The next query is convert those pcs that to dozens and multiply dozen cost (Biaya1) and pieces cost (Biaya).

The error :

Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Thank you.
you have:

SUM(B.Biaya1 * SUM(QtyLsn + CAST ... )

you cannot do a SUM ( SUM ) ...

either you do:
SUM(B.Biaya1) * SUM(QtyLsn + CAST- )
or:
SUM(B.Biaya1 * ( QtyLsn + CAST ... )

emi_sastra

Hi angelIII,

Please see below data :

EmpCode   BarangCode                       PekerjaanCode    TglTransaksi                     QtyLsn   QtyPcs
BRD0006       CT 1026                             1001                      2011-05-02 00:00:00.000      0,00           40,00
BRD0006       CT 1026                             1001                      2011-05-12 00:00:00.000      12,00    56,00
BRD0006       CT 1041                             1002                      2011-05-04 00:00:00.000      1,00           0,00
BRD0006       CT 1041                             1002                      2011-05-05 00:00:00.000      1,00           0,00
BRD0006       CT 1041                             1002                      2011-05-06 00:00:00.000      1,00           0,00
BRD0006       SHERA 13                            1005                      2011-05-12 00:00:00.000      26,00     8,00

using :

SUM(A.QtyLsn + CAST(A.QtyPcs / 12 AS INTEGER)) AS QtyLsn,
SUM(CAST(A.QtyPcs % 12 AS INTEGER)) AS QtyPcs

I get :

BRD0006       CT 1026                             1001            2011-05-02 00:00:00.000      3,00             4
BRD0006       CT 1026                             1001            2011-05-12 00:00:00.000      17,00      8

What's wrong with my code ?

Thank you.

I am not sure that SUM() is what you need to do, as you join to a table (B), while the columns you take to SUM() is from A.
if you run the query without GROUP BY and SUM, you will eventually see that you have "duplicates", and you need to run the query first to get no duplicates.
once you have that, you can take that as "input" to make your SUMs ...
emi_sastra

The sum error has been solved, but another problem still happened.

1. BRD0006       CT 1026                             1001                      2011-05-02 00:00:00.000      0,00           40,00
2. BRD0006       CT 1026                             1001                      2011-05-12 00:00:00.000      12,00    56,00

1. QtyLsn = 0, QtyPcs = 40
2. QtyLsn = 12, QtyPcs = 56

The result should be :

1. QtyLsn = 3, QtyPcs = 4
2. QtyLsn = 16, QtyPcs = 8

But using below code :

SUM(A.QtyLsn + CAST(A.QtyPcs / 12 AS INTEGER)) AS QtyLsn,
SUM(CAST(A.QtyPcs % 12 AS INTEGER)) AS QtyPcs

get:

1. QtyLsn = 3, QtyPcs = 4
2. QtyLsn = 17, QtyPcs = 8  This is wrong.

QtyLsn means Dozens
QtyPcs means Pieces.

Thank you.

A.QtyPcs % 12

should that not be / instead of % ?
emi_sastra

Which line of code ?

SUM(A.QtyLsn + CAST(A.QtyPcs / 12 AS INTEGER)) AS QtyLsn --> This code convert pcs into dozens
SUM(CAST(A.QtyPcs % 12 AS INTEGER)) AS QtyPcs --> This code get remain pcs.

Thank you.
If I suggest to replace % by / , I wonder why you ask in which line of code?
emi_sastra

A.QtyPcs % 12

56  % 12  = 8
56  / 12  = 4

It has different result and purpose, that's why I ask.

Thank you.
% is modulo operator:
56 / 12 = 4.xxx
56 % 12 = 8, because 56 - (4x12) = 8

I don't think you want to use %, hence my suggestion to use /
emi_sastra

I use % to get the remain pcs and it is the purpose.

QtyDzs   QtyPcs
12         56

Expected Result

QtyDzs   QtyPcs
16         8

16 = 12 + (56/12) = 16
8 = 56 % 12 = 8

What do you think ?

Thank you.
I see.
you have to change to first do the sum, then the / or %

SUM(A.QtyLsn) + CAST(SUM(A.QtyPcs) / 12 AS INTEGER)) AS QtyLsn,
CAST(SUM(A.QtyPcs) % 12 AS INTEGER) AS QtyPcs
emi_sastra

Yes, the code has no error, but the result is wrong.
Very weird.

QtyDzs   QtyPcs
17         8

Thank you.
emi_sastra

Below code works well.

SELECT SUM( 12 + CAST(56 / 12 AS INTEGER)) AS QtyLsn,
CAST(56 % 12 AS INTEGER) AS QtyPcs

QtyDzs   QtyPcs
16         8

Thank you.
emi_sastra

CAST(SUM(A.QtyPcs) / 12 AS INTEGER)  AS ResultQtyPcs

Get 5 not 4.

Thank you.
please try FLOOR() function instead of CAST ...
emi_sastra

SELECT
SUM(QtyPcs) AS QtyPcs,
SUM(QtyLsn) AS QtyDzs,
CAST(SUM(A.QtyPcs) / 12 AS INT)  AS ResultDzsFromPcs

QtyPcs      QtyDzs      ResultDzsFromPcs
40,00      0,00              3
56,00      12,00      5

Weird right ?

Thank you.
emi_sastra

Would please provide exact code to do ?

Thank you.
Guy Hengel [angelIII / a3]

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
prajapati84

Try this:

FLOOR(CAST(SUM(A.QtyPcs) / 12 AS INTEGER))  AS ResultQtyPcs

emi_sastra

Yes, it works.

By the way, why integer type round up decimal point, especially the decimal point greater and equal to .5 ?

CAST(SUM(A.QtyPcs) / 12 AS INT)  AS ResultDzsFromPcs
When qtypcs = 40, then it works well.
Using constant data and data from sql result differ.

Thank you.
CAST as INTEGER will round to the neareast integer ...
prajapati84

Yes, casting an interger will return the round value.
So, we use FLOOR or CEILING to the the required int value.
emi_sastra

Hi angelIII,

Thank you very much for your help.
emi_sastra

Hi prajapati84,

The problem has been solved by angelIII.

Thank you for your kindness.
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY