| Author |
 Topic  |
|
|
pickslides
Starting Member
Australia
6 Posts |
 Posted - 07/26/2012 : 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
USA
189 Posts |
Posted - 07/26/2012 : 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/ |
Edited by - sql-programmers on 07/26/2012 03:28:45 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 07/26/2012 : 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
Australia
6 Posts |
Posted - 07/26/2012 : 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
India
47023 Posts |
Posted - 07/27/2012 : 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/
|
|
|
| |
Topic |
|
|
|
SQL Query
