Zwei resultate "mischen"

[Shihan]

Hallo erstmal :-)

Ich habe folgende zwei Queries:
[highlight=sql]
SELECT STATE,COUNT(STATE) AS STATE_COUNT_ALL
FROM admin.TI$INV
GROUP BY STATE
ORDER BY STATE
[/highlight]

[highlight=sql]
SELECT STATE, COUNT(STATE) AS STATE_COUNT_OWN
FROM admin.TI$INV
WHERE BUSDIV IN (0020, 0022, 0024, 0040, 0090)
GROUP BY STATE
ORDER BY STATE
[/highlight]

Ich würde draus gerne ein einziges machen, dass mir folgende Spalten zurückgibt:
| STATE | STATE_COUNT_ALL | STATE_COUNT_OWN |

quasi der Status, dann die gesammt Anzahl (Rechnungen) pro Status und in der letzen Spalte nur die Rechnungs-Anzahl welche in einem bestimmten BUKRS sind.

Ich habe schon mit diversen JOINS rumgeübt oder mit UNION, doch es kam nie so raus wie ich wollte. Irgendwie denke ich, dass kann ja nicht so schwer sein (habe eigentlich schon einige Jahre SQL Erfahrung),.. aber vermutlich ist es noch zu früh am Morgen :-)

Evtl. hat einer von euch ne Idee oder kann mich auf die richtige Spur bringen?

Danke und Gruss, Shi

[Shihan]

Also ich bin mal weiter gekommen, jedoch zeigt es mir nun für jeden status zwei Zeilen an, einmal mit ausgefüllter .._ALL Spalte einmal mit ausgefüllter _OWN Spalte, das ganze müsste also noch gruppiert werden. Gemäss einem Beitrag hier im Forum habe ich das mal so umgebaut:

[highlight=sql]
WITH Summary(STATE, STATE_COUNT_ALL, STATE_COUNT_OWN) AS
(
SELECT STATE, COUNT(STATE) AS STATE_COUNT_ALL, 0 STATE_COUNT_OWN
FROM admin.TI$INV
GROUP BY STATE
UNION
SELECT STATE, 0,COUNT(STATE) AS STATE_COUNT_OWN
FROM admin.TI$INV
WHERE BUSDIV IN (4100)
GROUP BY STATE
)
SELECT STATE, SUM(STATE_COUNT_ALL), SUM(STATE_COUNT_OWN) FROM Summary
GROUP BY STATE
[/highlight]

Leider erhalte ich einen Error beim ausführen. Kann mir das nicht erklären, da es im Beispiel ja auch funktioniert und in der SQL-Hilfe hat es ein praktisch identisches Beispiel. Folgende Meldung kommt (die mal wieder gar nichts aussagt):
Meldung 156, Ebene 15, Status 1, Zeile 3
Incorrect syntax near the keyword 'WITH'.

Sieht jemand den Fehler von euch?

Gruss, Shi

[Bernd Schulz]

Hallo!

Erstmal ein paar Testdaten:
create table tempdb.dbo.data ( Art int, state varchar(10) )
insert into tempdb.dbo.data values ( 1, 'abc' )
insert into tempdb.dbo.data values ( 1, 'def' )
insert into tempdb.dbo.data values ( 2, 'abc' )
insert into tempdb.dbo.data values ( 2, 'def' )
insert into tempdb.dbo.data values ( 3, 'abc' )
insert into tempdb.dbo.data values ( 3, 'def' )
insert into tempdb.dbo.data values ( 4, 'abc' )

und jetzt die Abfrage
select t1.state, t1.countall, t2.countsub from
(select state, count(*) as countall from tempdb.dbo.data group by state) as t1,
(select state, count(*) as countsub from tempdb.dbo.data where art in(1,2) group by state) as t2
where t1.state = t2.state

So OK?

BYE BERND

[Shihan]

muss dass mal analysieren :-)

[Shihan]

Danke! das funktioniert nun wunderbar :-)

habe noch eine Tabelle hinzugezogen welche alle States enthält und das mit nem Outer Join verknüpft, das ganze sieht nun so aus (für die die es interessiert):
[highlight=sql]
SELECT
invstates.STATE_ID AS STATE
,ISNULL(t1.COUNTALL, 0) AS COUNT_ALL
,ISNULL(t2.COUNTSUB, 0) AS COUNT_SUB
FROM
(SELECT
STATE
,COUNT(*) AS COUNTALL
FROM
admin.TI$INV
GROUP BY STATE) AS t1 INNER JOIN
(SELECT
STATE
,COUNT(*) AS COUNTSUB
FROM
admin.TI$INV
WHERE
(BUSDIV IN (1000, 1001, 1003, 3400, 4000, 4400, 4401, 4402, 4500, 5101, 5200))
GROUP BY STATE) AS t2 ON t1.STATE = t2.STATE
RIGHT OUTER JOIN
admin.TIC$INVSTATE AS invstates ON t1.STATE = invstates.STATE_ID
ORDER BY invstates.STATE_ID
[/highlight]