T-SQL select statement...SUM and HAVING issue

Question

I have a table similar to below...

I need to add the values of each row with the exact same date. (For example the first two rows will equal '5', next two, '5', next two, '8')

StoretCode   LocName    ParName   DateTime       Value
95010-A-9    AB 901_4   On Line   201305160000   1
NULL         AB 905_8   On Line   201305160000   4
95010-A-9    AB 901_4   On Line   201305170000   2
NULL         AB 905_8   On Line   201305170000   3
95010-A-9    AB 901_4   On Line   201305180000   4
NULL         AB 905_8   On Line   201305180000   4

The result should also have a new LocName...so it should look like...

StoretCode   LocName  ParName  DateTime      Value
95010-A-9    AERBAS   On Line  201305160000  5
95010-A-9    AERBAS   On Line  201305170000  5
95010-A-9    AERBAS   On Line  201305180000  8

I have tried...

Select 'AB 901_4','AB 905_8', Sum(Value)
from dbo.DATA

but the results were as expected...one row with a total of all rows...

Also tried.....

Select 'AB 901_4','AB 905_8', Sum(Value)
from dbo.DATA
Where LocName, DateTime = LocName, DateTime

but result was...

Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near ','.

tried several others but result was similar to non-boolean msg above...

Where does the new LocName come from? Is it a constant value that all rows will get or does it depend on the original value? — Patrick Marchand
– Patrick Marchand, Commented Aug 9, 2013 at 18:31
I'm sorry...I will need to create the new LocName and delete the rows that were added together... — user2668701
– user2668701, Commented Aug 9, 2013 at 18:44
Why don't you group by datetime. Select 'AB 901_4','AB 905_8', Sum(Value) from dbo.DATA Group by [DateTime] — Osa E
– Osa E, Commented Aug 9, 2013 at 18:59

Hellion · Accepted Answer · 2013-08-09 19:11:20Z

1

Perhaps something along these lines?

Select 
  max(StoretCode), 
  'AERBAS' as Locname, 
  max(ParName), 
  DateTime, 
  Sum(Value)
from dbo.DATA
group by DateTime
order by DateTime

answered Aug 9, 2013 at 19:11

Hellion

1,74028 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

paparazzo · Accepted Answer · 2013-08-09 18:56:49Z

1

Select StoretCode , 'AERBAS' as LocName , ParName , DATA.DateTime, datecount.count as [value]
from dbo.DATA 
join ( select DateTime, count(*) as count from dbo.DATA group by DateTime ) datecount 
  on data.DateTime = datecount.DateTime 
where date.StoretCode is not null

If you want a hard coded value for LocName then 'LocName'

edited Aug 9, 2013 at 18:56

answered Aug 9, 2013 at 18:19

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

5 Comments

user2668701 Over a year ago

Thanks, but that groups everything by StoretCode and removed the 'Value' column name...the actual table I am working on has 74 distinct StoretCodes...I want to add the rows with the LocNames I mentioned.

paparazzo Over a year ago

Don't know how to help you as this answers the question as I understand it.

paparazzo Over a year ago

How is this output different from the desired output?

user2668701 Over a year ago

To be a little more specific...I am actually working on a table with 74 StoretCodes (including the NULL), 95 LocNames (so many have NULL StoretCodes)...I need to combine the two LocNames I mentioned in my example...

paparazzo Over a year ago

Against the posted sample data does this query produce the correct results or not? Not clear to me what combine the two LocNames means.

Collectives™ on Stack Overflow

T-SQL select statement...SUM and HAVING issue

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related