Summarized Result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Summarized Result

Author

Topic

ranadeepg
Starting Member

3 Posts

Posted - 2011-06-02 : 10:01:56

I have Table name Shipmentbags. The data in the table like below -
Size SentWt ReceivedWt Remainingwt Authorised
+3 100.00 99.01 .99 Y
+3 100.00 100.00 0.00 Y
+3 100.00 98.04 1.96
+5 100.00 99.01 .99 Y
+5 100.00 100.00 0.00 Y

If all row in the above table is authorized then in the output screen the summarized rows will be green. (below the row of +5 will be green)

I have to show these data (summarized)
Size SentWt ReceivedWt Remainingwt
+3 300.00 297.05 2.95
+5 200.00 199.01 .99

Now if the data became
Size SentWt ReceivedWt Remainingwt Authorised
+3 100.00 99.01 .99 Y
+3 100.00 100.00 0.00 Y
+3 100.00 98.04 1.96 Y
+5 100.00 99.01 .99 Y
+5 100.00 100.00 0.00 Y

Then output will be (all the row will be green)
Size SentWt ReceivedWt Remainingwt
+3 300.00 297.05 2.95
+5 200.00 199.01 .99

I want to use a single query for return the output data with a tag based on which the color of the grid will be changed.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-02 : 10:10:14

[code]
select Size, sum(SentWt), sum(ReceivedWt), sum(Remainingwt),
green = case when min(isnull(Authorised, 'N')) = 'N' then 'N' else 'Y' end
from yourtable
group by Size
[/code]

KH
[spoiler]Time is always against us[/spoiler]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-02 : 10:13:22

Use this query:

SELECT Size, SUM(SentWt) SentWt, SUM(ReceivedWt) ReceivedWt, SUM(Remainingwt) Remainingwt, 
CASE WHEN COUNT(*)=SUM(CASE WHEN Authorized='Y' THEN 1 END) THEN 1 ELSE 0 END AllAuthorized
FROM Shipmentbags
GROUP BY Size

You can't apply different colors to results in Management Studio, but you can in a report. Put a conditional expression for foreground color on the AllAuthorized column, it should be green if it equals 1.

ranadeepg
Starting Member

3 Posts

Posted - 2011-06-03 : 05:12:41

quote:
Originally posted by robvolk

Use this query:
SELECT Size, SUM(SentWt) SentWt, SUM(ReceivedWt) ReceivedWt, SUM(Remainingwt) Remainingwt,
CASE WHEN COUNT(*)=SUM(CASE WHEN Authorized='Y' THEN 1 END) THEN 1 ELSE 0 END AllAuthorized
FROM Shipmentbags
GROUP BY Size
You can't apply different colors to results in Management Studio, but you can in a report. Put a conditional expression for foreground color on the AllAuthorized column, it should be green if it equals 1.

THANKS ROBVOLK

ranadeepg
Starting Member

3 Posts

Posted - 2011-06-03 : 05:13:11

quote:
Originally posted by robvolk

Use this query:
SELECT Size, SUM(SentWt) SentWt, SUM(ReceivedWt) ReceivedWt, SUM(Remainingwt) Remainingwt,
CASE WHEN COUNT(*)=SUM(CASE WHEN Authorized='Y' THEN 1 END) THEN 1 ELSE 0 END AllAuthorized
FROM Shipmentbags
GROUP BY Size
You can't apply different colors to results in Management Studio, but you can in a report. Put a conditional expression for foreground color on the AllAuthorized column, it should be green if it equals 1.

THANKS KHTAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources