July 17, 2002 at 8:24 am
Hi everybody!
Maybe somebody could give me a hint for this grouping select statement
Here is an example. I have 2 columns: ID and value
ID Value
1111 1
1112 2
1113 3
2011 1
2013 2
2014 1
2015 2
2016 1
2017 2
2018 1
2019 2
2020 1
2021 2
2022 1
2023 2
2024 1
2025 2
So, I would like to group this values after the following criteria:
1. group all items where ID starts with 11
2. group all items where ID is between 2015 and 2025
and sum the value
The result should be like
expr1 6
expr2 17
Thanks a lot,
durug
July 17, 2002 at 8:50 am
hi, try this
select sum([value]) from tbltest
where left([id],2)=11
group by left([id],2)
union all
select sum([value]) from tbltest
where [id] between 2015 and 2025
group by left([id],2)
July 17, 2002 at 8:55 am
Is ID numeric? If so try: -
select
'expr1'
,sum(Value)
From
Tab
where
Id between 1100 and 1199
union allselect
'expr2'
,sum(Value)
From
Tab
where
Id between 2015 and 2025
Regards,
Andy Jones
.
July 17, 2002 at 7:34 pm
Thank a lot to both of you. Both solution work. Still Klaas-Jan I don't know if I really need the group option in your select statement. It's working also without it.
I noticed a small delay when running the script. My final table will contain 5000 records and will have like 20 grouping criteria's.
Hopefully the performance will not go dramatically down.
Thanks one more time,
Durug
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply