CODEWITHSUNDEEP
sql-server-2008
Yasin
Yasin

Reputation: 1

Crystal report - 2 logic in single column

We have requirement in Crystal report where we have to implement 2 concept in one column. For eg: in below screenshot,  if we have numbers we need to sum up and display as one row.  if it is variable row. It should remain as single row.

Material    Quantity    Price
O Ring      1           5.61
O Ring      1           Fixed 
O Ring      1           5.61
O Ring      1           Fixed

Output should be,

Material    Quantity    Price
O Ring      1           11.22
O Ring      1           Fixed 
O Ring      1           Fixed

Kindly pour us some suggestions if any.

Upvotes: 0

Views: 27

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

First note that your current table design is very flawed, because you are trying to store numeric and text information in the same Price column, both as text. This means that to sum up any prices, we have to cast to a number, and then cast back again to text to report with the other text only values. You would be much better off not storing text in the Price column, and making that column numeric.

That being said, the following query appears to do what you want:

SELECT
    Material, MAX(Quantity) AS Quantity,
    CAST(SUM(CAST(Price AS numeric(9,2))) AS varchar(15)) AS Price
FROM yourTable
WHERE Price <> 'Fixed'
GROUP BY Material
UNION ALL
SELECT Material, Quantity, Price
FROM yourTable
WHERE Price = 'Fixed';

enter image description here

Demo

Upvotes: 1

Related Questions