abfrage mit count und group

[lan4lano]

Hallo liebes Forum,da hier ja richtige spezialisten zu sein schein, dürfte das ein leichtes sein.
Ich hab eine tabelle mit user, programm:
user1, rtl
user2, rtl
user1, vox
user1, vox
ergebniss soll sein:
2 user , rtl
1 user, vox
Ich hab mich schon bis auf:
$query = "SELECT programm, count(DISTINCT user) FROM daten GROUP BY programm, user";
rangekämpft, es kommt aber:
1 user , rtl
1 user, rtl
1 user , vox
jemand ne idee dazu?
vielen dank -sven-

 

[thomas_w]

AW: abfrage mit count und group

Dein SQL war ja schon fast richtig.

Folgendes Beispiel dazu:

CREATE TABLE test_programme (
 user VARCHAR(10) NOT NULL,
 programm  VARCHAR(10) NOT NULL
);
 
INSERT INTO test_programme VALUES
( 'user1', 'rtl'),
( 'user2', 'rtl'),
( 'user1', 'vox'),
( 'user1', 'vox');
 
SELECT programm, COUNT(DISTINCT user) AS anzahl_user
  FROM test_programme
 GROUP BY programm
 ORDER BY programm;
+----------+-------------+
| programm | anzahl_user |
+----------+-------------+
| rtl      |           2 |
| vox      |           1 |
+----------+-------------+
2 rows in set (0.00 sec)
mysql>

Ich habe mal MySQL verwendet, obwohl dies hier die MS-SQL-Server Gruppe ist.

Grüße
Thomas

 

[Charly]

AW: abfrage mit count und group

Hallo,

und wenn mann dann in der Spalte user "2 user" stehen haben möchte kann man das Statement noch um folgendes erweitern.

RTRIM(CAST((COUNT (DISTINCT Benutzer)) AS NCHAR)) + ' user' AS [user]

Gruß Charly

 

[Charly]

AW: abfrage mit count und group

Hallo,

soll natürlich so aussehen:

RTRIM(CAST(COUNT (DISTINCT [user]) AS NCHAR)) + ' user' AS [user]

Gruß Charly

 

[lan4lano]

AW: abfrage mit count und group

Funktioniert, Dankeschön.
und wenn mann dann in der Spalte user "2 user" stehen haben möchte
jetzt nicht übertreiben
trotzdem danke, vllt. kann ichs nochmal irgendwann brauchen
-Sven-

 

[lan4lano]

AW: abfrage mit count und group

Moin,
hätt da nochmal eine frage. auch wenn ich ausversehn die falsche kategorie erwischt habe, kann ja ein admin ggf. verschieben

Wie in der ersten frage hab ich immer noch eine tabelle mit user und programm.

user; programm
user1 rtl
user2 rtl
user1 vox
user1 vox

nun kann es 1...n user geben und ca 200 Programme.
ich möchte nun die 5 meist gesehenen Sender raus suchen und anzeigen.
Dazu könnt ich die abfrage aus der ersten frage ja um "Limit 1,5" erweitern.
jetzt kommts, die restlichen sender die nicht unter den meisgeschauten 5 sind möchte ich als sonstige zusammen fassen. ergebniss sollte dann ca. so sein.
10 rtl
8 vox
6 pro7
4 sat1
4 kabel1
17 sonstige

geht sowas überhaupt? wenn ja wäre ein anschauliches beispiel recht herzlich wilkommen.

vielen dank -sven-

 

[Charly]

AW: abfrage mit count und group

Hallo,

als allererstes fällt mir da ein UNION ein.

einmal nur die ersten 5 und
einmal die ersten 5 rausgefiltert.

Ich kann mich dunkel erinnern das es auch eleganter zu lösen geht.

Wenns mir wieder einfällt schreibe ich nochmal.

Gruß Charly

PS: Habe gerade nicht so viel Zeit. Wenn ich ein bisschen Luft habe kommt das SQL auch noch.

 

[Charly]

AW: abfrage mit count und group

Hallo,

nicht schön aber selten.

Das Programm 'Sonstige' habe ich einfach mal erfunden. Dann habe ich im ersten Teil die TOP 1 (nur für diese Beispiel) von der Gesamtanzahl der user abgezogen.
Dann mit der ursprünglichen Liste die auf die TOP 1 (auch nur für diese Beispiel) reduziert ist, mit UNION zusammengeführt.

Ein SQL-Guru bekommt das bestimmt eleganter gelöst.

SELECT 'Sonstige' AS programm,
(
SELECT SUM(anzahl_user) FROM
 (
 SELECT programm, COUNT(DISTINCT [user]) AS anzahl_user 
 FROM test_programme 
 GROUP BY programm 
 )AS h1
)
- 
(
SELECT SUM(anzahl_user) FROM
 (
 SELECT TOP 1 programm, COUNT(DISTINCT [user]) AS anzahl_user 
 FROM test_programme 
 GROUP BY programm 
 ORDER BY anzahl_user DESC
 ) AS h2
)AS anzahl_user
 
UNION
 
SELECT programm, anzahl_user FROM 
(
 SELECT TOP 1 programm, COUNT(DISTINCT [user]) AS anzahl_user 
 FROM test_programme 
 GROUP BY programm 
 ORDER BY anzahl_user DESC
)AS h3

Ich habe das nur mit den 4 Testdatensätzen getestet.

Ergebins:

programm     anzahl_user
rtl           2
Sonstige      1

Bitte mal mit den richtigen Daten testen.

Gruß Charly

 

[lan4lano]

AW: abfrage mit count und group

Moin,
erstmal danke.
2. ach du scheiße. Jetzt wirds ja richtig haarig

abgesehn davon das ich durch die abfrage überhaupt nicht mehr durchblicke, bekomm ichs auch nicht angepasst für die orginal daten.
Ich poste mal meine Tabelle:

CREATE TABLE IF NOT EXISTS `daten` (
`ID` bigint(20) NOT NULL auto_increment,
`user` text character set utf8 NOT NULL,
`datum` int(8) NOT NULL,
`zeit` varchar(4) character set utf8 NOT NULL,
`programm` varchar(12) character set utf8 NOT NULL,
PRIMARY KEY (`ID`),
KEY `zeit` (`zeit`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=99584 ;

INSERT INTO `daten` (`ID`, `user`, `datum`, `zeit`, `programm`) VALUES
(1, '1', 20100912, '0000', '2717f001d161'),
(2, '2', 20100912, '0000', '43100016e42'),
(3, '3', 20100912, '0000', '2718f001d175'),
(4, '4', 20100912, '0000', '2718f001d176'),
(5, '5', 20100912, '0000', '2717f001d161'),
(6, '6', 20100912, '0000', '43100016e42'),
(7, '7', 20100912, '0000', '43100016e44'),
(8, '8', 20100912, '0000', '2714f001d171'),
(9, '9', 20100912, '0000', '43100016e42'),
(10, '10', 20100912, '0000', '43100016e44');

-Sven-

 

[Charly]

AW: abfrage mit count und group

Hallo,

erstmal sorry, das war MS-SQL. MySQL benutzt LIMIT statt TOP.
Das sieht dann mit der Daten-Tabelle so aus:

SELECT 'Sonstige' AS programm,
(
SELECT SUM(anzahl_user) FROM
 (
 SELECT programm, COUNT(DISTINCT user) AS anzahl_user 
 FROM daten 
 GROUP BY programm 
 )AS h1
)
- 
(
SELECT SUM(anzahl_user) FROM
 (
 SELECT programm, COUNT(DISTINCT user) AS anzahl_user 
 FROM daten  
 GROUP BY programm 
 ORDER BY anzahl_user DESC 
 LIMIT 1,5
 ) AS h2
)AS anzahl_user
 
UNION
 
SELECT programm, anzahl_user FROM 
(
 SELECT programm, COUNT(DISTINCT user) AS anzahl_user 
 FROM daten 
 GROUP BY programm 
 ORDER BY anzahl_user DESC 
 LIMIT 1,5
)AS h3
ORDER BY programm

Und hat als Ergebnis

programm       user
2714f001d171   1
2717f001d161   2
2718f001d175   1
2718f001d176   1
43100016e44    2
Sonstige       3

So das war die Pflicht. Jetzt kann man sich gedanken über die Optimierung machen. So ein Haufen verschachtelter SELECTs kostet ne Menge Performance.

Gruß Charly

 

[lan4lano]

AW: abfrage mit count und group

Nanü...

erstmal sorry, das war MS-SQL. MySQL benutzt LIMIT statt TOP.

Brauchst dich doch nicht zu entschuldigen, ich bin doch der der hier doof fragen stellt. Davon ab, es scheint ja beides sehr ähnlich zu sein, da kann sowas ja nunmal schnell passieren.

zurück zum problem.
erstmal vielen dank, es funktioniert.
zweitens haste glück das du nicht hier bist, ich würd dich erstmal dicke knutschen
drittens, ich hab es mal mit 500.000 datensätzen probiert, das ganze in php, also das das lange dauert kann ich nicht sagen, funktioniert gefühlt genauso schnell wie für 500.

Nochmals ganz ganz recht herzlichen dank dafür.

Falls ich nochmal eine frage habe meld ich mich bestimmt, wenn keine Frage mehr kommt meld ich mich wenn alles fertig ist und präsentiere mal das ergebniss, zu dem du ja nun auch einen großteil beigetragen hast.

Dankeschön erstmal bis hier hin -Sven-

 

[thomas_w]

AW: abfrage mit count und group

...
Und hat als Ergebnis

programm       user
2714f001d171   1
2717f001d161   2
2718f001d175   1
2718f001d176   1
43100016e44    2
Sonstige       3

So das war die Pflicht. Jetzt kann man sich gedanken über die Optimierung machen. So ein Haufen verschachtelter SELECTs kostet ne Menge Performance.

Gruß Charly

Hallo Charly,

eine Super-SQL-Lösung finde ich. Da wäre ich erst einmal nicht drauf gekommen.

Danke, mal wieder was gelernt.

Grüße
Thomas