Need Help on SQL query script

[Yuexuan]

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:

Seq Unit ID Value
1   1       1000
2   1       100
3   11      300
4   11      1000
5   11      110
6   11      300
7   3       999
8   3       1
9   4       500
10  4       400
11  5       1200
12  6       1000
13  6       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

Seq Unit ID     Value
1   1           1100 (1000+100)
2   11          1000
3   11          1100 (300+110+300)
4   3           1000 (999+1)
5   5           1200
6   6           1100

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!

[pritaeas]

Something like:

SELECT *, SUM(`Value`) FROM `table` GROUP BY `UnitID` HAVING SUM(`Value`) >= 1000

Untested, but should get you going.

[Yuexuan]

It does not work as result shown as below with
Input:

1.     Seq Unit ID Value
2.     1   1       1000
3.     2   1       100
4.     3   11      300
5.     4   11      1000
6.     5   11      110
7.     6   11      300
8.     7   3       999
9.     8   3       1
10.    9   4       500
11.    10  4       400
12.    11  5       1200
13.    12  6       1000
14.    13  6       100
15.    14  11      300  

Result with Select unitID,sum(Value) from table group by unitID having sum(value)>= 1000

1. UnitID   Sum(Value)
2.  3       1000
3.  5       1200    
4.  1       1100        
5. 11       2010
6.  6       1100

The UnitId does not spare 2 line with value 1010 and 1000. Any idea how to spare out?

Any expert can advice ?Thanks

[pritaeas]

What's the logic behing separating that value? If it is exactly 1000 it should be a single line?

[Yuexuan]

if the sigle value >= 1000 should be a single line
If the sum out amount per ID >= 1000 in a single line .
Expected output as below:

Seq Unit ID     Value
1   1           1100 (1000+100)
2   11          1000 (exact value >= 1000)
3   11          1100 (300+110+300)
4   3           1000 (999+1)
5   5           1200 (exact value >= 1000)
6   6           1100 (1000+100)

UnitID 11 should shown 2 line as first value hit >=1000
secord line is sum out with >= 1000

[SQLpower]

That should work:

SELECT DISTINCT id, sum(`number`)
FROM numbers
WHERE `number` >= 1000

UNION

SELECT DISTINCT id, `number` AS amount
FROM numbers
WHERE `number` < 1000
ORDER BY id;

[pritaeas]

if the sigle value >= 1000 should be a single line

I understand you did this for ID 11 (although 300+300+110 equals 710, not 1010), yet NOT for ID's 1 and 6, so you must have more rules than you showed.

I got no further than this, unless you specify more rules:

SELECT unit, SUM(`value`) as `value`
FROM `dw457186`
WHERE `value` < 1000
GROUP BY `unit`
HAVING SUM(`value`) >= 1000

UNION

SELECT `unit`, `value`
FROM `dw457186`
WHERE `value` >= 1000

ORDER BY `unit`

[Yuexuan]

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 ?

[pritaeas]

The first part of my query above should get you started.

[Yuexuan]

My I know which query are you to?

[Yuexuan]

My I know which query are you refer to?Sorry for missing typo previous post