select query as column data - 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

select query as column data

Author

Topic

darbar
Starting Member

16 Posts

Posted - 2013-08-08 : 13:41:14

Outer query: select op.const_cd, (select const_hin from constituency c where c.const_cd=op.const_cd)
from PollingStation op, constituency c
where op.const_cd=c.const_cd
group by op.const_cd
order by op.const_cd
Result is:
122 a 205
123 b 205
124 c 235
125 d 191
126 e 226
127 f 159
128 g 165
129 h 175
130 i 225
131 j 213

Inner Query result of: select const_cd, count(polling_cd) from PollingStation p where right(polling_cd,1)='A' group by p.const_cd order by p.const_cd:
122 4
123 4
124 2
125 3
126 1
127 6
128 11
129 9
130 2

I want this output:
const_cd const_name count inner query
122 a 205 4
123 b 205 4
124 c 235 2
125 d 191 3
126 e 226 1
127 f 159 6
128 g 165 11
129 h 175 9
130 i 225 2
131 j 213

Please help me.
Thanks in advance.
Looking forward to receive jet help.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 13:59:12

Try this:
[CODE]

SELECT
T1.const_cd, Const_Name, T2.[Count]
FROM
(select
op.const_cd,
(select
const_hin
from
constituency c
where
c.const_cd = op.const_cd) as Const_Name
from
PollingStation op, constituency c
where
op.const_cd = c.const_cd
group by op.const_cd
order by op.const_cd) T1
LEFT JOIN
(select
const_cd, count(polling_cd) as [Count]
from
PollingStation p
where
right(polling_cd, 1) = 'A'
group by p.const_cd
order by p.const_cd) T2 ON T1.const_cd = T2.const_cd

[/CODE]

darbar
Starting Member

16 Posts

Posted - 2013-08-08 : 14:07:16

Thanks for your reply. but still I am getting this error:
Msg 1033, Level 15, State 1, Line 17
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'order'.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 14:16:51

My bad:
Try this:
[CODE]

SELECT
T1.const_cd, Const_Name, T2.[Count]
FROM
(select
op.const_cd,
(select
const_hin
from
constituency c
where
c.const_cd = op.const_cd) as Const_Name
from
PollingStation op, constituency c
where
op.const_cd = c.const_cd
group by op.const_cd) T1
LEFT JOIN
(select
const_cd, count(polling_cd) as [Count]
from
PollingStation p
where
right(polling_cd, 1) = 'A'
group by p.const_cd) T2 ON T1.const_cd = T2.const_cd
order by T1.const_cd

[/CODE]

darbar
Starting Member

16 Posts

Posted - 2013-08-08 : 14:18:58

Thanks bro. Its working fine. How can I use count() and sum() in addition to existing query.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 14:24:24

What do you want to sum() or count()?

darbar
Starting Member

16 Posts

Posted - 2013-08-08 : 22:13:22

I want Sum()