QUERY PROBLEM

Do you get any specific error?

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 ... )

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 ...

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 % ?

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?

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 /

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

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

QtyDzs   QtyPcs
17         8

Thank you.

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.

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

Get 5 not 4.

Thank you.

please try FLOOR() function instead of CAST ...

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.

Would please provide exact code to do ?

Thank you.

Try this:

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

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 ...

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

Hi angelIII,

Thank you very much for your help.

Hi prajapati84,

The problem has been solved by angelIII.

Thank you for your kindness.