How to get count of two different fields with group by in MySQL?

Question

I have a "4 table joined sql query". There are relations between them. Tables names are, users, documents, document_type, document_type_group. documents table has all id named uniq identifiers from other tables. I try to get counts of documents in document_types and total count of document_type_groups. (Table names are Turkish, sorry for that) I can get count of document_type_groups with the query below:

SELECT eTG.evrak_tipi_grup_adi, eT.evrak_tipi, COUNT(eTG.evrak_tipi_grup_adi) AS toplamGrup
FROM evraklar evr
LEFT JOIN kullanicilar kull ON evr.evrak_kaydeden_ybs_kullanici_id=kull.id
LEFT JOIN evrak_tipleri eT ON evr.evrak_tipi=eT.id
LEFT JOIN evrak_tipi_gruplari eTG ON evr.evrak_tipi_grubu=eTG.id
GROUP BY eTG.evrak_tipi_grup_adi

and I can get count of document_type with:

SELECT eTG.evrak_tipi_grup_adi, eT.evrak_tipi, COUNT(eT.evrak_tipi) AS toplamTiptekiler
FROM evraklar evr
LEFT JOIN kullanicilar kull ON evr.evrak_kaydeden_ybs_kullanici_id=kull.id
LEFT JOIN evrak_tipleri eT ON evr.evrak_tipi=eT.id
LEFT JOIN evrak_tipi_gruplari eTG ON evr.evrak_tipi_grubu=eTG.id
GROUP BY eT.evrak_tipi

I wish to combine these querys to show them on same table to user. Any clue?

please provide CREATE TABLE as well as INSERT INTO commands so as to easily reproduce your situation — Alex Jurado - Bitendian
– Alex Jurado - Bitendian, Commented Feb 7, 2014 at 12:16
sqlfiddle.com does the trick. But just pasting your CREATE and INSERT statements here is enough. That's the tough part. — Alex Jurado - Bitendian
– Alex Jurado - Bitendian, Commented Feb 7, 2014 at 12:26
anonfiles.com/file/963fe3665d1de1f564aec61410d56e46 the sql file includes table creates. — caglaror
– caglaror, Commented Feb 7, 2014 at 12:27
Why are the two statements aggregating by different fields? Why aren't both aggregating by eTG.evrak_tipi_grup_adi, eT.evrak_tipi? — Gordon Linoff
– Gordon Linoff, Commented Feb 7, 2014 at 12:42

Kickstart · Accepted Answer · 2014-02-08 21:45:39Z

1

Looking at your SQL, I am a bit unsure what you want to occur.

Both pieces retrieve eTG.evrak_tipi_grup_adi and eT.evrak_tipi, but one groups by one , while the 2nd groups by the other. If the values vary of the non grouped field vary for a grouped field then the value that is returned is from an unspecified row, so is likely to be meaningless.

If the values do not vary (ie, any record on evrak_tipleri only has a single record on evrak_tipi_gruplari - presume docuement type and document type group) then your current queries could be written as :-

SELECT eTG.evrak_tipi_grup_adi, eT.evrak_tipi, COUNT(eTG.evrak_tipi_grup_adi) AS toplamGrup
FROM evraklar evr
LEFT JOIN kullanicilar kull ON evr.evrak_kaydeden_ybs_kullanici_id=kull.id
LEFT JOIN evrak_tipleri eT ON evr.evrak_tipi=eT.id
LEFT JOIN evrak_tipi_gruplari eTG ON evr.evrak_tipi_grubu=eTG.id
GROUP BY eTG.evrak_tipi_grup_adi, eT.evrak_tipi

SELECT eTG.evrak_tipi_grup_adi, eT.evrak_tipi, COUNT(eT.evrak_tipi) AS toplamTiptekiler
FROM evraklar evr
LEFT JOIN kullanicilar kull ON evr.evrak_kaydeden_ybs_kullanici_id=kull.id
LEFT JOIN evrak_tipleri eT ON evr.evrak_tipi=eT.id
LEFT JOIN evrak_tipi_gruplari eTG ON evr.evrak_tipi_grubu=eTG.id
GROUP BY eTG.evrak_tipi_grup_adi, eT.evrak_tipi

so could just be merged to:-

SELECT eTG.evrak_tipi_grup_adi, eT.evrak_tipi, COUNT(eTG.evrak_tipi_grup_adi) AS toplamGrup, COUNT(eT.evrak_tipi) AS toplamTiptekiler
FROM evraklar evr
LEFT JOIN kullanicilar kull ON evr.evrak_kaydeden_ybs_kullanici_id=kull.id
LEFT JOIN evrak_tipleri eT ON evr.evrak_tipi=eT.id
LEFT JOIN evrak_tipi_gruplari eTG ON evr.evrak_tipi_grubu=eTG.id
GROUP BY eTG.evrak_tipi_grup_adi, eT.evrak_tipi

The count will just bring back the number of non null values in those fields though. I suspect you might want to use COUNT(DISTINCT....) instead.

However this very much depends on exactly what you want to count and how the tables relate to each other (ie, one to many relationships, etc)

EDIT

Ignoring users for now, but I think this is what you want (using the table and column names from the sql fiddle):--

SELECT doc_types.id, 
        doc_types.doc_type_name, 
        Sub1.doc_type_group_name, 
        COUNT(documents.id) AS DocsForDocType,
        Sub1.DocsForDocTypeGroup
FROM doc_types
LEFT OUTER JOIN documents
ON doc_types.id = documents.doc_type_id
LEFT OUTER JOIN
(
    SELECT doc_type_groups.id, doc_type_groups.doc_type_group_name, COUNT(documents.id) AS DocsForDocTypeGroup
    FROM doc_type_groups
    INNER JOIN documents 
    ON doc_type_groups.id = documents.doc_type_group_id
    GROUP BY doc_type_groups.id, doc_type_group_name
) Sub1
ON doc_types.doc_type_group_id = Sub1.id
GROUP BY doc_types.id, doc_types.doc_type_name, Sub1.doc_type_group_name, Sub1.DocsForDocTypeGroup;

This is getting the document types and the number of documents for that type, while also getting the document group for the document type and the number of documents in that group.

edited Feb 8, 2014 at 21:45

answered Feb 7, 2014 at 12:42

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

caglaror Over a year ago

I am going to try DISTINCT in COUNT.

caglaror Over a year ago

Here the result of last sql: image.caglarorhan.com/2014/2/7/88608681775372471512.jpg But i want to get toplamGrup as a cumulative count of toplamtiptekiler. That means i need counts of document_types and count of document_type_groups.

Kickstart Over a year ago

I am a bit confused about exactly what you want. Can you put up some example data and the results you want from it.

caglaror Over a year ago

I try to get a result includes doc_types names, count of documents under that type, and count of documents under that group of types.

caglaror Over a year ago

Any help? I have any solutions in my hand :/

|

Collectives™ on Stack Overflow

How to get count of two different fields with group by in MySQL?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related