SQL 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

General SQL Server Forums

New to SQL Server Programming

SQL Query

Author

Topic

pickslides
Starting Member

7 Posts

Posted - 2012-07-26 : 01:37:29

Hi all, very new to SQL queries

I have the following code and output. I am trying to fliter some fields then sum a few with the remaining records

Code:

select * from TEMP_MQ1 where [TARIFF TYPE]='R' and Days >0 and KL >0 
sum(TEMP_MQ1.[KL]) AS SumOfKL, sum(TEMP_MQ1.[DAYS]) AS SumOfDAYS
From temp_mq1 
Group by TEMP_MQ1.[ACCOUNT NUMBER];

Output:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'sum'.

cheers, MQ

Be who you are and say what you feel,
because those who mind don't matter
and those who matter don't mind.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-07-26 : 03:26:39

USE CPI_LCP_July6
I think your data will be

quote:

[ACCOUNT NUMBER] [Days] [KL]
1 2 1
1 3 2
2 6 2
3 8 1
3 2 1

and
your result will be

[ACCOUNT NUMBER] [Days] [KL]
1 2 1
1 3 2
--------------------------------
Total 5 3
--------------------------------
2 6 2
--------------------------------
Total 6 2
--------------------------------
3 8 1
3 2 1
--------------------------------
Total 10 2
--------------------------------

So Use this query IF not exact result pls explain what yo want with example DATA,

SELECT [ACCOUNT NUMBER] ,
[Days] ,
[KL]
FROM ( SELECT 1 AS Sno ,
[ACCOUNT NUMBER] ,
[Days] ,
[KL]
FROM TEMP_MQ1
WHERE [TARIFF TYPE] = 'R'
AND Days > 0
AND KL > 0
UNION
SELECT 2 AS Sno ,
[ACCOUNT NUMBER] ,
SUM(TEMP_MQ1.[KL]) AS [Days] ,
SUM(TEMP_MQ1.[DAYS]) AS [KL]
FROM TEMP_MQ1
WHERE [TARIFF TYPE] = 'R'
GROUP BY TEMP_MQ1.[ACCOUNT NUMBER]
) AS tbl
ORDER BY [ACCOUNT NUMBER], Sno

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-26 : 10:13:41

looks like what you're after is this


select TEMP_MQ1.[ACCOUNT NUMBER],  
sum(TEMP_MQ1.[KL]) AS SumOfKL, 
sum(TEMP_MQ1.[DAYS]) AS SumOfDAYS
From temp_mq1 
where [TARIFF TYPE]='R' 
and Days >0 and KL >0
Group by TEMP_MQ1.[ACCOUNT NUMBER];

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pickslides
Starting Member

7 Posts

Posted - 2012-07-26 : 19:44:39

quote:
Originally posted by visakh16

looks like what you're after is this

select TEMP_MQ1.[ACCOUNT NUMBER],
sum(TEMP_MQ1.[KL]) AS SumOfKL,
sum(TEMP_MQ1.[DAYS]) AS SumOfDAYS
From temp_mq1
where [TARIFF TYPE]='R'
and Days >0 and KL >0
Group by TEMP_MQ1.[ACCOUNT NUMBER];

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hi there, thanks for your help, this query ran well. Also thanks to the help provided in post two.

If I wanted to edit the query to change the condition of Days >0 to days are between 42 and 100 how would this work?

Kind regards, MQ

Be who you are and say what you feel,
because those who mind don't matter
and those who matter don't mind.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 00:50:24

quote:
Originally posted by pickslides

quote:
Originally posted by visakh16

looks like what you're after is this

select TEMP_MQ1.[ACCOUNT NUMBER],
sum(TEMP_MQ1.[KL]) AS SumOfKL,
sum(TEMP_MQ1.[DAYS]) AS SumOfDAYS
From temp_mq1
where [TARIFF TYPE]='R'
and Days >0 and KL >0
Group by TEMP_MQ1.[ACCOUNT NUMBER];

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hi there, thanks for your help, this query ran well. Also thanks to the help provided in post two.

If I wanted to edit the query to change the condition of Days >0 to days are between 42 and 100 how would this work?

Kind regards, MQ

Be who you are and say what you feel,
because those who mind don't matter
and those who matter don't mind.

why dont you try the change and see first?

just put what you told in suggestion like below


select TEMP_MQ1.[ACCOUNT NUMBER],  
sum(TEMP_MQ1.[KL]) AS SumOfKL, 
sum(TEMP_MQ1.[DAYS]) AS SumOfDAYS
From temp_mq1 
where [TARIFF TYPE]='R' 
and Days BETWEEN 42 AND 100 
and KL >0
Group by TEMP_MQ1.[ACCOUNT NUMBER];

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources