Need Help on SQL query script - 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

General SQL Server Forums

New to SQL Server Programming

Need Help on SQL query script

Author

Topic

yuexuan
Starting Member

8 Posts

Posted - 2013-06-22 : 03:08:13

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!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-22 : 10:47:42

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?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-23 : 09:02:24

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

yuexuan
Starting Member

8 Posts

Posted - 2013-06-23 : 09:18:56

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 00:54:37

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-24 : 02:52:37

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"

yuexuan
Starting Member

8 Posts

Posted - 2013-06-28 : 11:08:26

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 ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 11:14:18

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

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-28 : 11:21:50

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

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 11:32:37

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-29 : 04:29:03

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"

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-29 : 11:16:39

Can't this work without loops:
[CODE]
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

[/CODE]

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"

yuexuan
Starting Member

8 Posts

Posted - 2013-06-29 : 13:05:31

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-29 : 13:47:44

[CODE]

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];
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-29 : 14:11:55

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

yuexuan
Starting Member

8 Posts

Posted - 2013-06-29 : 14:12:51

quote:
Originally posted by MuMu88

[CODE]

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];
[/CODE]

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-29 : 14:22:47

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:
[CODE]

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
[/CODE]

yuexuan
Starting Member

8 Posts

Posted - 2013-06-29 : 14:27:23

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-29 : 14:29:16

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

yuexuan
Starting Member

8 Posts

Posted - 2013-06-29 : 14:30:55

quote:
Originally posted by yuexuan

quote:
Originally posted by MuMu88

[CODE]

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];
[/CODE]

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

Just for my curiosity,may I know any reason why using MAX function?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-29 : 14:35:42

You have to use aggregate function on the columns that are not part of group by clause.

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources