logo
down
shadow

My SQL - COUNT field on multitable group by other field


My SQL - COUNT field on multitable group by other field

By : goldman
Date : October 22 2020, 08:10 AM
should help you out I missed to create the correct request. , First do a union and then group by:
code :
select actionreal, count(distinct departement) nb_actions from ( 
    select actionreal, departement
    from militant_action_nutrition 
    where actionreal in (1, 2, 3) 
    union 
    select actionreal, departement 
    from militant_action_jna 
    where actionreal in (1, 2, 3)
) t
group by actionreal


Share : facebook icon twitter icon
JPA Is there a way to do something like SELECT <field>, count(*) FROM <table> GROUP BY <field>

JPA Is there a way to do something like SELECT <field>, count(*) FROM <table> GROUP BY <field>


By : user2024666
Date : March 29 2020, 07:55 AM
this will help I'm not sure I understood the question correctly but doesn't the following JPQL query do what you want:
code :
SELECT p.name, count(p) from Product p group by p.name
How can I get a count field next to the aggregated group by field in MongoDB?

How can I get a count field next to the aggregated group by field in MongoDB?


By : pintorjo
Date : March 29 2020, 07:55 AM
wish of those help I'm working with the restaurant sample data set where the data is like: , Try this
code :
db.restaurants.aggregate([
  {
    "$group" : {_id:"$cuisine", count_1:{$sum:1}}
  }
]).
result.
map(function(o) {
  var r = {};
  r[o._id] = o.count_1;
  return r;
});
(MySQL) Group by field and select both COUNT(field) and number of grouped rows

(MySQL) Group by field and select both COUNT(field) and number of grouped rows


By : Katherine Vervack
Date : March 29 2020, 07:55 AM
To fix this issue You can add another level of grouping around your first grouped query.
code :
SELECT id_cnt, COUNT(*) AS grp_cnt 
FROM (
    SELECT COUNT(*) AS id_cnt 
    FROM test.test GROUP BY id) id_cnts
GROUP BY id_cnt;
Count field and group by regarding another field on same table MySQL

Count field and group by regarding another field on same table MySQL


By : Angela Thomas
Date : March 29 2020, 07:55 AM
I hope this helps you . I am trying to find count of field by another field in same table on MySQL.My Table Like This:
code :
SELECT t2.Code, 
        SUM(t1.CodeType = 'I') AS EkTaniSay,
        SUM(t1.CodeType = 'A') AS IslemSay
FROM TIGPatientCode AS t1
RIGHT JOIN TIGPatientCode AS t2 ON t1.DrgPatientId = t2.DrgPatientId
WHERE t1.isPrincipal != 1 AND t2.isPrincipal = 1
GROUP BY t1.DrgPatientId;
SELECT t.Code, v1.EkTaniSay, v2.IslemSay
FROM TIGPatientCode t
LEFT JOIN (
    SELECT DRGPatientId, COUNT(*) AS EkTaniSay
    FROM TIGPatientCode
    WHERE IsPrincipal<>'1' AND CodeType='I'
    GROUP BY DRGPatientId
) AS v2 ON t.DRGPatientId = v2.DRGPatientId
LEFT JOIN (
    SELECT DRGPatientId, COUNT(*) AS IslemSay
    FROM TIGPatientCode
    WHERE IsPrincipal<>'1' AND CodeType='A'
    GROUP BY DRGPatientId
) AS v1 ON t.DRGPatientId = v1.DRGPatientId
WHERE t.IsPrincipal = 1
MongoDB aggregate with group by nested field and count by another field

MongoDB aggregate with group by nested field and count by another field


By : user1611840
Date : March 29 2020, 07:55 AM
This might help you You would need to run the following aggregate pipeline to get the desired results:
code :
db.getCollection('test').aggregate([
    /*
    1. Create a field with an array of the summary details key concatenated with their 
       corresponding values. 
    */
    { "$addFields": { 
        "summary": {
            "$map": {
                "input": { "$objectToArray": "$summary.details" },
                "as": "el",
                "in": {
                    "$concat": ["$$el.k", "_", "$$el.v"]
                }
            }
        }
    } },

    /*
    2. Flatten the new array to produce a copy of each document per array entry. 
    */
    { "$unwind": "$summary" },

    /*
    3. Group the documents initially by the key and category. 
    */
    { "$group": {
        "_id": { 
            "key": "$summary",
            "category": "$category"
        },
        "count": { "$sum": 1 }
    } },

    /*
    4. Group the input documents from the previous pipeline by the key and aggregate the 
       category and corresponding counts  
    */
    {  "$group": {
        "_id": "$_id.key",
        "counts": {
            "$push": {
                "category": "$_id.category",
                "count": "$count"
            }
        }
    } },

    /*
    4. Calculate accumulated values for all the input documents as a whole.
    */
    {  "$group": {
        "_id": null,
        "counts": {
            "$push": {
                "k": "$_id",
                "v": "$counts"
            }
        }
    } },
    { "$replaceRoot": {
        "newRoot": { "$arrayToObject": "$counts" }
    } } 
])
Related Posts Related Posts :
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • VARCHAR TIME TO GET THE DIFFERENCE
  • SQL conditional constraint on multiple columns being unique
  • Optimize a SQL select query in a loop
  • BTEQ Teradata Import Multiple files into one table
  • Update SQL datetime column with oldest values of another table column?
  • Is INSERT ... SELECT an atomic transaction?
  • SQL query completed successfully but not results
  • SQL sub select returning multiple values
  • Verify condition on two columns
  • SQL conditional field, first match JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org