Simplify my query - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Simplify my query

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-01-24 : 00:11:20

Hi guys,

This query returns the exact result ..

But i want to simplify thus query ..
Is it possible ?

select COUNT(1) cnt,SUM(amount) amt from tbl_analysis_amount where sample_id like 'fer%' union all
select COUNT(1) cnt,SUM(amount) amt from tbl_analysis_amount where sample_id like 'soi%' union all
select COUNT(1) cnt,SUM(amount) amt from tbl_analysis_amount where sample_id like 'wtr%' union all
select COUNT(1) cnt,SUM(amount) amt from tbl_analysis_amount where sample_id like 'sam%' union all
select COUNT(1) cnt,SUM(amount) amt from tbl_analysis_amount where sample_id like 'pst%' union all
select COUNT(1) cnt,SUM(amount) amt from tbl_analysis_amount where sample_id like 'plt%' union all
select COUNT(1) cnt,SUM(amount) amt from tbl_analysis_amount where sample_id like 'mad%' union all
select COUNT(1) cnt,SUM(amount) amt from tbl_analysis_amount where sample_id like 'egy%'

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-01-24 : 00:39:07

Maybe. I can't see how it makes sense though. How do you know which row is which without including the sample ID?

You could try something like:

select count(*), sum(amount) from tbl_analysis_amount group by left(sample_id, 3) where left(sample_id,3) in ('fer','soi','wtr',...)

but if it works, why change it?

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-01-24 : 00:53:09

I got this error msg
Incorrect syntax near the keyword 'where'.

I am trying to improve my knowledge in writing queries

And thanks for your reply

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-01-24 : 00:57:29

select COUNT(1) cnt,SUM(amount) amt,'fer' from tbl_analysis_amount where sample_id like 'fer%' union all

And I tried like this to differentiate

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-01-24 : 01:06:07

Yeah sorry you need to get the SQL basics right! :)
select count(*), sum(amount) from tbl_analysis_amount where left(sample_id,3) in ('fer','soi','wtr',...) group by left(sample_id, 3)
If you want to get the sample_ID for the column then simply select the bit:

select count(*), sum(amount), left(sample_id,3) as sampleIDprefix from tbl_analysis_amount where left(sample_id,3) in ('fer','soi','wtr',...) group by left(sample_id, 3)

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-01-24 : 01:08:57

Thank you ...

I also tried this and got the result

select count(*) cnt, sum(amount) amt,left(sample_id,3) type from tbl_analysis_amount

where left(sample_id,3) in ('fer','soi','wtr') group by left(sample_id, 3)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources