February 12, 2009 at 9:48 am
I've the followin code:
select
(
select vestiging.naam, count(*) as Aantal_Incidenten
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid
group by vestiging.naam
),
(
select vestiging.naam, count(*) as Aantal_Inc_Portal
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid and soortbinnenkomstid='0k0001'
group by vestiging.naam
)
What I'm trying to get, are 3 colums: vestiging.naam, Aantal_Incidenten, Aantal_Inc_Portal
When I'm running this query I've got the error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I'm looking for a solution for this.....
February 12, 2009 at 10:57 am
select
ALIAS1.naam,
ALIAS1.Aantal_Incidenten,
ALIAS2.naam,
ALIAS2.Aantal_Inc_Portal
FROM
(
select vestiging.naam, count(*) as Aantal_Incidenten
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid
group by vestiging.naam
) ALIAS1,
(
select vestiging.naam, count(*) as Aantal_Inc_Portal
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid and soortbinnenkomstid='0k0001'
group by vestiging.naam
)ALIAS2
Lowell
February 13, 2009 at 1:21 am
Lowell, thanks for you support.
The query runs, but I get the next result/issue:
customer1 5customer11
customer2 3customer11
customer3 7customer11
customer4 2customer11
customer5 8customer11
customer6 11customer26
customer7 2customer26
customer8 6customer26
customer9 3customer26
In other words, the second subquery results are not combined with the first subquery results.
February 13, 2009 at 1:47 am
You have to specify how to join the derived tables ALIAS1 and ALIAS2. Currently you are using both in the from clause without specifying how to join them. This causes the usage of cross join. If you’ll add the part that defines the inner join between the derived tables, it will work as you want it to
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 13, 2009 at 2:22 am
I hope this is what you mean:
In table "incident" there is a field "aanmeldervestigingid" that links to table "vestiging" with field "unid" (It translate a code to a customer name)
In table "incident" I get the count of the fields with and without "soortbinnenkomstid='0k0001'" that are related to the field "aanmeldervestigingid"
Regards, Harm...
February 18, 2009 at 12:32 pm
Still having this problem. Can someone help me?
February 18, 2009 at 12:43 pm
If you'll show us the code that you are using and explain what you are getting, you'll have a much better chance to get help.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 18, 2009 at 12:54 pm
In my first post I've posted my code:
select
(
select vestiging.naam, count(*) as Aantal_Incidenten
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid
group by vestiging.naam),
(
select vestiging.naam, count(*) as Aantal_Inc_Portal
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid and soortbinnenkomstid='0k0001'
group by vestiging.naam)
What I'm trying to get, are 3 colums: vestiging.naam, Aantal_Incidenten, Aantal_Inc_Portal
When I'm running this query I've got the error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
So, I've used the code posted by Lowell:
Select
ALIAS1.naam,
ALIAS1.Aantal_Incidenten,
ALIAS2.naam,
ALIAS2.Aantal_Inc_Portal
FROM
(
select vestiging.naam, count(*) as Aantal_Incidenten
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid
group by vestiging.naam
) ALIAS1,
(
select vestiging.naam, count(*) as Aantal_Inc_Portal
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid and soortbinnenkomstid='0k0001'
group by vestiging.naam
)ALIAS2
This code runs but I've got the next result:
customer1 5 customer1 1
customer2 3 customer1 1
customer3 7 customer1 1
customer4 2 customer1 1
customer5 8 customer1 1
customer6 11 customer2 6
customer7 2 customer2 6
customer8 6 customer2 6
customer9 3 customer2 6
What I've like the get is:
Naam Aantal Inc Aantal Inc Portal
customer1 5 1
customer2 3 6
etc....
I hope this makes it clear.
Thanks in advance....
February 18, 2009 at 1:00 pm
like others said, you have to JOIN the two aliased groups:
Select
ALIAS1.naam,
ALIAS1.Aantal_Incidenten,
--ALIAS2.naam, --now that they are joined, no need to repeat
ALIAS2.Aantal_Inc_Portal
FROM
(
select vestiging.naam, count(*) as Aantal_Incidenten
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid
group by vestiging.naam
) ALIAS1 INNER JOIN
(
select vestiging.naam, count(*) as Aantal_Inc_Portal
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid and soortbinnenkomstid='0k0001'
group by vestiging.naam
)ALIAS2 ON ALIAS1.naam = ALIAS2.naam
Lowell
February 18, 2009 at 1:02 pm
Remove the "Alias2.naam" column from the select statement and you should have what you want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 18, 2009 at 1:16 pm
Ok..... I didn't understood the first reply from Adi.
I've tested and now I've got a good result, but.....
Can it be, that if the second subquery has no results, that there a no results displayed for the first subquery?
I've expected that there where a lot of 0 (zero's) in the second column.
February 18, 2009 at 1:19 pm
Replace the Inner Join with Full Outer Join, and do this to the numeric columns:
IsNull(ALIAS1.Aantal_Incidenten, 0) as Aantal_Incidenten
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 18, 2009 at 1:23 pm
I understand the part of FULL OUTER JOIN, but I'm not sure what to do with "IsNull(ALIAS1.Aantal_Incidenten, 0) as Aantal_Incidenten" (newbie...)
February 18, 2009 at 1:25 pm
htilburgs (2/18/2009)
Ok..... I didn't understood the first reply from Adi.I've tested and now I've got a good result, but.....
All you had to do as ask for clarifications:-)
Can it be, that if the second subquery has no results, that there a no results displayed for the first subquery?
I've expected that there where a lot of 0 (zero's) in the second column.
If you use an inner join this is exactly what should happen. Inner join will bring back only the records that have the same value on the columns that the join is based on. If you want to get all the records from 1 table even if there are no matching records in the second table, you’ll have to use an outer join. Do you want to get a different results? If you do, you have to specify what you expect to get.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 18, 2009 at 1:29 pm
Select
isnull(ALIAS1.naam, ALIAS2.naam) as naam,
isnull(ALIAS1.Aantal_Incidenten, 0) as Aantal_Incidenten,
isnull(ALIAS2.Aantal_Inc_Portal, 0) as Aantal_Inc_Portal
FROM
(
select vestiging.naam, count(*) as Aantal_Incidenten
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid
group by vestiging.naam
) ALIAS1 FULL OUTER JOIN
(
select vestiging.naam, count(*) as Aantal_Inc_Portal
from incident, vestiging
where incident.aanmeldervestigingid = vestiging.unid and soortbinnenkomstid='0k0001'
group by vestiging.naam
)ALIAS2 ON ALIAS1.naam = ALIAS2.naam
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply