SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need Help on SQL query script
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

yuexuan
Starting Member

8 Posts

Posted - 06/22/2013 :  03:08:13  Show Profile  Reply with Quote
Hey guys,
New to the site, new to writing SQL queries and have no background in this type of thing.
But I've been googling my way to victory for the most part.

I'm struggling on probably a very simple problem.
I'd like to Sum the contents of a column, if a different value in the row is the same as in another... That was worded horribly - how about an illustration:

Table
The value may not in the order
Unit ID---------Value
-------------------------
10000-----------1000
10000-----------100
10010-----------600
10010-----------1000
10010-----------110
10010-----------300
10030-----------999
10030-----------1
10040-----------500
10040-----------400
10050-----------1200
10060-----------1000
10060-----------100


So basically I'm looking for an output when I run the script that shows
Value must >= 1000 after sum up with unit ID.Unit ID shown again if first value hit 1000
Unit ID---------Value
-------------------------
10000-----------1100 (1000+100)
10010-----------1000
10010-----------1100 (600+110+300)
10030-----------1000 (999+1)
10050-----------1200
10060-----------1100 (1000+100)

And various other simplistic queries, but at this point I'm willing to admit that I haven't a clue what I'm doing.

Any help would be appreciated,
Thanks!

Edited by - yuexuan on 06/23/2013 07:04:56

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 06/22/2013 :  10:47:42  Show Profile  Reply with Quote
There is no order to data stored in a table in SQL Server database. You have one row for Unit Id =10000, presumably because in the example that you posted, the values are ordered as 1000 and then 10. But SQL Server has no way of knowing that, and two rows for UnitId = 10000, with 10000 and 11000 would also be possible.

Is there some way (i.e., another column or columns that can be used) to order the rows so the results can be deterministic?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 06/23/2013 :  09:02:24  Show Profile  Reply with Quote
I think you're looking for a simple group by:

select [unit id], sum([value]) as valueSum
from <yourTAble>
group by [unit id]


Be One with the Optimizer
TG
Go to Top of Page

yuexuan
Starting Member

8 Posts

Posted - 06/23/2013 :  09:18:56  Show Profile  Reply with Quote
not as simply group by.Need to separate to second record if the first value hit >= 1000 and second record sum out with >= 10000
expected output as below:
Unit ID---------Value
-------------------------
10000-----------1100 (1000+100)
10010-----------1000
10010-----------1100 (600+110+300)
10030-----------1000 (999+1)
10050-----------1200
10060-----------1100 (1000+100)

The unit Id 10010 appear 2 time record.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/24/2013 :  00:54:37  Show Profile  Reply with Quote
sounds like this to me
your output is not consistent though. for 10060 also you've 2 records for 1000 and 110 where you summed up together whereas for 10010 with same condition you chose to split them as 1000 in one and rest into another record (with value 1010 which you're showing as 1100, a typo i reckon)


SELECT unitID,SUM(Value) AS Total
FROM Table
GROUP BY UnitID,CASE WHEN Value=1000 THEN 1 ELSE 2 END



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30104 Posts

Posted - 06/24/2013 :  02:52:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
As I understand the requirements, you want ">=1000 value buckets", right?
That's why 10010 and 10060 is consistent.

For sample data

10070-----------900
10070-----------800
10070-----------700
10070-----------600

what is the correct output?

10070-----------1500 (900+600)
10070-----------1500 (800+700)

or

10070-----------1600 (900+700)
10070-----------1400 (800+600)

or

10070-----------1700 (900+800)
10070-----------1300 (700+600)

?


N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 06/24/2013 02:53:32
Go to Top of Page

yuexuan
Starting Member

8 Posts

Posted - 06/28/2013 :  11:08:26  Show Profile  Reply with Quote
Hi All,
After review again expected output with my client ,they confirm and come out the new requirement as per below:

Input Table:
1. ID Value
2. 1 20
3. 1 30
4. 1 20
5. 1 30
6. 1 10
7. 2 51
8. 3 50
9. 4 20
10. 4 30
11. 4 10
12. 4 5
13. 4 50
14. 5 5
15. 5 50
16. 6 5
17. 6 3
18. 6 50
19. 7 35
20. 7 4
Any ID aggregated of values >= 50 need to shown and the indicator flag shown as "Y" for only one ID value >= 50.If the sigle value >= 50 should be a single line

Expected output :

1. ID Value IND
2. 1 110(20+30+20+30+10) N
3. 2 51 Y
4. 3 50 Y
5. 4 115(20+30+10+5+50) N
6. 5 55 (50+5) N
7. 6 58 (50+8) N
Thanks,Any expert can advice ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/28/2013 :  11:14:18  Show Profile  Reply with Quote

SELECT ID, SUM(Value),
CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END AS IND
FROM Table
GROUP BY ID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/28/2013 :  11:21:50  Show Profile  Reply with Quote
select id, sum(value),case when Max(value) >= 50 then 'Y' Else 'N' END FROM TableName

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/28/2013 :  11:32:37  Show Profile  Reply with Quote
quote:
Originally posted by MIK_2008

select id, sum(value),case when Max(value) >= 50 then 'Y' Else 'N' END FROM TableName

Cheers
MIK


Not matching OP's output as the above logic is giving Y for IDs 4,5,6

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30104 Posts

Posted - 06/29/2013 :  04:29:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This is a n-p problem named "Bin Packaging".
And most probably not possible done with one query. You will need a loop of some kind.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/29/2013 :  11:16:39  Show Profile  Reply with Quote
Can't this work without loops:

DECLARE @temp TABLE (ID INT, VALUE INT)
INSERT INTO @temp VALUES
(1, 20),
(1, 30),
(1, 20),
(1, 30),
( 1, 10),
( 2, 51),
( 3, 50),
(4, 20),
(4, 30),
( 4, 10),
( 4, 5),
( 4, 50),
( 5, 5),
( 5, 50),
( 6, 5), 
( 6, 3),
 ( 6, 50),
( 7, 35),
( 7, 4),
(8, 25);

SELECT ID, SUM(Value),
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN 'Y' ELSE 'N' END AS IND
FROM @temp
GROUP BY ID



quote:
Originally posted by SwePeso

This is a n-p problem named "Bin Packaging".
And most probably not possible done with one query. You will need a loop of some kind.



N 56°04'39.26"
E 12°55'05.63"


Go to Top of Page

yuexuan
Starting Member

8 Posts

Posted - 06/29/2013 :  13:05:31  Show Profile  Reply with Quote
Thanks for reply, But my client require another requirement, wonder it can be done easier?


1. Unit ID Value orderid orderdate
2. 1 20 12 20121121
3. 1 30 13 20121121
4. 1 20 14 20121121
5. 1 30 15 20121120
6. 1 10 16 20121121
7. 2 51 17 20121113
8. 3 50 18 20121114
9. 4 20 19 20121112
10. 4 30 20 20121112
11. 4 10 21 20121112
12. 4 5 22 20121112
13. 4 50 23 20121112
14. 5 5 25 20121110
15. 5 50 26 20121111
16. 6 5 27 20121112
17. 6 3 28 20121112
18. 6 50 29 20121114
19. 7 35 30 20121112
20. 7 4 31 20121112

Output:
aggregated of Value must >= 50 after sum up with ID.The flag indicator will tag with 'Y' if only when single unit >= 50 without sum with each other.
The single unit >=50 will shown orderID and orderdate, not single unit >=50(sum up with other value) will shown nothing.
Expected output :

1. ID Value Indicator flag OrderID orderDate
2. 1 110(20+30+20+30+10) N
3. 2 51 Y 17 20121113
4. 3 50 Y 18 20121114
5. 4 115(20+30+10+5+50) N
6. 5 55 (50+5) N
7. 6 58 (50+8) N

Edited by - yuexuan on 06/29/2013 22:44:50
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/29/2013 :  13:47:44  Show Profile  Reply with Quote


DECLARE @orders TABLE ([Unit ID] INT,  Value INT,  orderid INT,  orderdate DATE);
INSERT INTO @orders Values 
(1,20,12,'20121121'),
(1,30,13,'20121121'),
(1,20,14,'20121121'),
(1,30,15,'20121120'),
(1,10,16,'20121121'),
(2,51,17,'20121113'),
(3,50,18,'20121114'),
(4,20,19,'20121112'),
(4,30,20,'20121112'),
(4,10,21,'20121112'),
(4,5,22,'20121112'),
(4,50,23,'20121112'),
(5,5,25,'20121110'),
(5,50,26,'20121111'),
(6,5,27,'20121112'),
(6,3,28,'20121112'),
(6,50,29,'20121114'),
(7,35,30,'20121112'),
(7,4,31,'20121112');

SELECT [Unit ID], SUM(Value) Sumofvalues,
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN 'Y' ELSE 'N' END AS IND,
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN  'Y ' + CAST(MAX(orderid) as VARCHAR) 
	ELSE '('+STUFF((SELECT '+' + CAST(Value as VARCHAR) 
					FROM @orders T WHERE T.[Unit ID] = O.[Unit ID] 
					FOR XML PATH('')),1,1,'')+') N' END AS Orders,
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN MAX(orderdate) END as Orderdate
FROM @orders O
GROUP BY [Unit ID];
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/29/2013 :  14:11:55  Show Profile  Reply with Quote
quote:
Originally posted by yuexuan

Thanks for reply, But my client require another requirement, wonder it can be done easier?


1. Unit ID Value orderid orderdate
2. 1 20 12 20121121
3. 1 30 13 20121121
4. 1 20 14 20121121
5. 1 30 15 20121120
6. 1 10 16 20121121
7. 2 51 17 20121113
8. 3 50 18 20121114
9. 4 20 19 20121112
10. 4 30 20 20121112
11. 4 10 21 20121112
12. 4 5 22 20121112
13. 4 50 23 20121112
14. 5 5 25 20121110
15. 5 50 26 20121111
16. 6 5 27 20121112
17. 6 3 28 20121112
18. 6 50 29 20121114
19. 7 35 30 20121112
20. 7 4 31 20121112

Output:
aggregated of Value must >= 50 after sum up with ID.The flag indicator will tag with 'Y' if only when single unit >= 50 without sum with each other.
The single unit >=10 will shown orderID and orderdate, not single unit >=10(sum up with other value) will shown nothing.
Expected output :

1. ID Value Indicator flag OrderID orderDate
2. 1 110(20+30+20+30+10) N
3. 2 51 Y 17 20121113
4. 3 50 Y 18 20121114
5. 4 115(20+30+10+5+50) N
6. 5 55 (50+5) N
7. 6 58 (50+8) N



SELECT ID, SUM(Value),
CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END AS IND,
CASE WHEN COUNT(*) = 1 THEN MAX(OrderID) END AS  OrderID,
CASE WHEN COUNT(*) = 1 THEN MAX(OrderDate) END AS OrderDate
FROM Table
GROUP BY ID
HAVING SUM(Value) >=50


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 06/29/2013 14:41:34
Go to Top of Page

yuexuan
Starting Member

8 Posts

Posted - 06/29/2013 :  14:12:51  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88



DECLARE @orders TABLE ([Unit ID] INT,  Value INT,  orderid INT,  orderdate DATE);
INSERT INTO @orders Values 
(1,20,12,'20121121'),
(1,30,13,'20121121'),
(1,20,14,'20121121'),
(1,30,15,'20121120'),
(1,10,16,'20121121'),
(2,51,17,'20121113'),
(3,50,18,'20121114'),
(4,20,19,'20121112'),
(4,30,20,'20121112'),
(4,10,21,'20121112'),
(4,5,22,'20121112'),
(4,50,23,'20121112'),
(5,5,25,'20121110'),
(5,50,26,'20121111'),
(6,5,27,'20121112'),
(6,3,28,'20121112'),
(6,50,29,'20121114'),
(7,35,30,'20121112'),
(7,4,31,'20121112');

SELECT [Unit ID], SUM(Value) Sumofvalues,
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN 'Y' ELSE 'N' END AS IND,
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN  'Y ' + CAST(MAX(orderid) as VARCHAR) 
	ELSE '('+STUFF((SELECT '+' + CAST(Value as VARCHAR) 
					FROM @orders T WHERE T.[Unit ID] = O.[Unit ID] 
					FOR XML PATH('')),1,1,'')+') N' END AS Orders,
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN MAX(orderdate) END as Orderdate
FROM @orders O
GROUP BY [Unit ID];




Thank for reply, may I know what is
FOR XML PATH('')),1,1,'')+')
what is function using for?
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/29/2013 :  14:22:47  Show Profile  Reply with Quote
Gives you a list of values separated by a specified delimiter value, in this case '+'.

If you are interested you can read more about this topic here are search the internet:
http://msdn.microsoft.com/en-us/library/ms178107.aspx

Modified query where some duplicate data is removed:


SELECT [Unit ID], SUM(Value) Sumofvalues,
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN  CAST(MAX(orderid) as VARCHAR) 
	ELSE '('+STUFF((SELECT '+' + CAST(Value as VARCHAR) 
					FROM @orders T WHERE T.[Unit ID] = O.[Unit ID] 
					FOR XML PATH('')),1,1,'')+')' END AS Orders,
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN 'Y' ELSE 'N' END AS IND,
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN MAX(orderdate) END as Orderdate
FROM @orders O
GROUP BY [Unit ID]
HAVING SUM(Value) >=50;

Unit ID SumOfValues     Orders                     IND   OrderDate
1	    110	    (20+30+20+30+10)	          N	   NULL
2	    51	           17	                      Y    2012-11-13
3	    50	           18	                      Y    2012-11-14
4	    115	    (20+30+10+5+50)	          N	   NULL
5	    55	          (5+50)	                      N	   NULL
6	    58	          (5+3+50)	                      N	   NULL

Edited by - MuMu88 on 06/29/2013 20:09:58
Go to Top of Page

yuexuan
Starting Member

8 Posts

Posted - 06/29/2013 :  14:27:23  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

Gives you a list of values separated by a specified delimiter value, in this case '+'.

If you are interested you can read more about this topic here are search the internet:
http://msdn.microsoft.com/en-us/library/ms178107.aspx



May I know this function applicable for DB2 ? Since I now using IBM DB2 I-serial platform.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/29/2013 :  14:29:16  Show Profile  Reply with Quote
quote:
Originally posted by yuexuan

quote:
Originally posted by MuMu88

Gives you a list of values separated by a specified delimiter value, in this case '+'.

If you are interested you can read more about this topic here are search the internet:
http://msdn.microsoft.com/en-us/library/ms178107.aspx



May I know this function applicable for DB2 ? Since I now using IBM DB2 I-serial platform.


You should have specified this before

This is ms sql server forum so solutions given here are mostly sql server specific

The solution I gave is ANSI based so it may work in DB2. You can make a try but its not working your best bet is to try in some db2 related forums like www.dbforums.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

yuexuan
Starting Member

8 Posts

Posted - 06/29/2013 :  14:30:55  Show Profile  Reply with Quote
quote:
Originally posted by yuexuan

quote:
Originally posted by MuMu88



DECLARE @orders TABLE ([Unit ID] INT,  Value INT,  orderid INT,  orderdate DATE);
INSERT INTO @orders Values 
(1,20,12,'20121121'),
(1,30,13,'20121121'),
(1,20,14,'20121121'),
(1,30,15,'20121120'),
(1,10,16,'20121121'),
(2,51,17,'20121113'),
(3,50,18,'20121114'),
(4,20,19,'20121112'),
(4,30,20,'20121112'),
(4,10,21,'20121112'),
(4,5,22,'20121112'),
(4,50,23,'20121112'),
(5,5,25,'20121110'),
(5,50,26,'20121111'),
(6,5,27,'20121112'),
(6,3,28,'20121112'),
(6,50,29,'20121114'),
(7,35,30,'20121112'),
(7,4,31,'20121112');

SELECT [Unit ID], SUM(Value) Sumofvalues,
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN 'Y' ELSE 'N' END AS IND,
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN  'Y ' + CAST(MAX(orderid) as VARCHAR) 
	ELSE '('+STUFF((SELECT '+' + CAST(Value as VARCHAR) 
					FROM @orders T WHERE T.[Unit ID] = O.[Unit ID] 
					FOR XML PATH('')),1,1,'')+') N' END AS Orders,
CASE WHEN (COUNT(*) = 1 and SUM(Value) >=50) THEN MAX(orderdate) END as Orderdate
FROM @orders O
GROUP BY [Unit ID];




Thank for reply, may I know what is
FOR XML PATH('')),1,1,'')+')
what is function using for?



Just for my curiosity,may I know any reason why using MAX function?
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/29/2013 :  14:35:42  Show Profile  Reply with Quote
You have to use aggregate function on the columns that are not part of group by clause.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000