Sub 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

Sub Query

Author

Topic

Laylow
Starting Member

14 Posts

Posted - 2011-02-09 : 11:17:56

Hi All,

I am trying to retrive an aggregate for each client from a select query.

My first select query is as such:

SELECT COLUMN 1 AS CLIENT, SUM (AMOUNT) AS TOTAL
FROM TABLE
WHERE CONDITION
GROUP BY COLUMN 1

The Result is as follows:

COLUMN 1 AMOUNT
CBAT01 _____ 100
CBAT01_____ 150
CBEX01_____ 50
CBEX01_____ 300
CBOL01_____ 25
CBOL01_____ 75
CBOU01_____ 200
CBOU01_____ 10

I shall be most grateful if someone can show me how I can extract from these result, a summarized result as below:

COLUMN 1 AMOUNT
CBAT01 _____ 250
CBEX01_____ 350
CBOL01_____ 100
CBOU01_____ 210

I had a combine subquery like this below, but it didn't do the magic.

SELECT COLUMN 1, SUM (AMOUNT)
FROM TABLE
WHERE EXISTS
(SELECT COLUMN 1 AS CLIENT, SUM (AMOUNT) AS TOTAL
FROM TABLE
WHERE CONDITION
GROUP BY COLUMN 1)
GROUP BY COLUMN 1

Regards

Laylow

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-09 : 11:23:19

Check this

SELECT COLUMN 1 AS CLIENT, SUM (AMOUNT) AS TOTAL
FROM TABLE
GROUP BY COLUMN 1

Laylow
Starting Member

14 Posts

Posted - 2011-02-09 : 11:42:46

Hi MIK_2008,

I already tried this, it is what created my first result. For example the CBAT01 has two differnt condition resulting in the '100' and '50'.

COLUMN 1 AMOUNT
CBAT01 _____ 100
CBAT01_____ 150
CBEX01_____ 50
CBEX01_____ 300
CBOL01_____ 25
CBOL01_____ 75
CBOU01_____ 200
CBOU01_____ 10

What I would like to do now is consolidate this result in a subquery.

Regards

Laylow

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-09 : 11:55:29

Well what you asked can be done via above query ... for your ease i have done the whole example and execute/check the below code at once in SSMS

declare @tab table (col1 varchar(10),amount int)
insert into @tab values ('CBAT01',100),('CBAT01',150),('CBEX01',50),('CBEX01',300),('CBOL01',25),('CBOL01',75),('CBOU01',200),('CBOU01',10)
Select * from @tab

Select col1,sum(amount)
from @tab
group by col1

Can you place few rows in similar fashion and provide me the whole code along with your SELECT query so that i can check

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-09 : 20:55:24

quote:

SELECT COLUMN 1 AS CLIENT, SUM (AMOUNT) AS TOTAL
FROM TABLE
WHERE CONDITION
GROUP BY COLUMN 1

The Result is as follows:

COLUMN 1 AMOUNT
CBAT01 _____ 100
CBAT01_____ 150
CBEX01_____ 50
CBEX01_____ 300
CBOL01_____ 25
CBOL01_____ 75
CBOU01_____ 200
CBOU01_____ 10

There is no way that above query will give that result. Looking at your earlier thread, the GROUP BY statement should have another column
GROUP BY [COLUMN 1], [TYPE]. Then it is possible to give the above result

quote:
I shall be most grateful if someone can show me how I can extract from these result, a summarized result as below:

COLUMN 1 AMOUNT
CBAT01 _____ 250
CBEX01_____ 350
CBOL01_____ 100
CBOU01_____ 210

If your query is as what you have posted (as shown below), then you would have got this result


SELECT [COLUMN 1] AS CLIENT, SUM (AMOUNT) AS TOTAL
FROM TABLE
WHERE CONDITION
GROUP BY [COLUMN 1]

KH
[spoiler]Time is always against us[/spoiler]

Laylow
Starting Member

14 Posts

Posted - 2011-02-10 : 04:57:34

Hi khtan & MIK_2008,

The below is the exact query I have:

SELECT LEFT(ACCNT_CODE,6),SUM (AMOUNT * -1)
FROM TABLE NAME
WHERE JRNAL_TYPE IN ('ALP', 'ALR') AND ACCNT_CODE LIKE 'C_____P___' AND DUE_DATETIME BETWEEN '2011-02-28' AND '2011-03-31'
AND ALLOCATION = ''
GROUP BY ACCNT_CODE, JRNAL_TYPE

This actually works and produces the result I am looking for below:

CBAT01 1119.040
CBAT01 531.340
CBEX01 380.010
CBEX01 15.000
CBEX01 189.990
CBEX01 140.010
CBEX01 442.500

How can I then get this to consolidate the result and group by Client e.g. CBAT01 etc.

The below is the exact Query I tried to execute without success:
SELECT ACCNT_CODE, SUM (AMOUNT)
FROM TABLE NAME
WHERE =
(SELECT LEFT(ACCNT_CODE,6),SUM (AMOUNT * -1)
FROM SLX_A_SALFLDG
WHERE JRNAL_TYPE IN ('ALP', 'ALR') AND ACCNT_CODE LIKE 'C_____P___' AND DUE_DATETIME BETWEEN '2011-02-28' AND '2011-03-31'
AND ALLOCATION = ''
GROUP BY ACCNT_CODE, JRNAL_TYPE)

Regards

Laylow

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-10 : 05:05:22

i don't understand what are you trying to achive in your 2nd query.

but if you want to produce result like

quote:

I shall be most grateful if someone can show me how I can extract from these result, a summarized result as below:

COLUMN 1 AMOUNT
CBAT01 _____ 250
CBEX01_____ 350
CBOL01_____ 100
CBOU01_____ 210

just change your query to


SELECT LEFT(ACCNT_CODE,6),SUM (AMOUNT * -1)
FROM TABLE NAME
WHERE JRNAL_TYPE IN ('ALP', 'ALR') AND ACCNT_CODE LIKE 'C_____P___' AND DUE_DATETIME BETWEEN '2011-02-28' AND '2011-03-31'
AND ALLOCATION = ''
GROUP BY ACCNT_CODE, JRNAL_TYPE LEFT(ACCNT_CODE,6)

KH
[spoiler]Time is always against us[/spoiler]

Laylow
Starting Member

14 Posts

Posted - 2011-02-10 : 05:26:45

Wow,

Thanks to you both, that worked magic!

Query has been put to bed.

Regards

Laylow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources