CODEWITHSUNDEEP
mysqlsqlsequelize.js
Muhammad Aadil Banaras
Muhammad Aadil Banaras

Reputation: 21

Group by in SQL OR Sequelize which where check and where in count as well

I have a table with columns id, type, type(this can be test1,test2, test3)

it need a query or sequelize solution as i have added both options in my code, query should give me result like. Group by url & give me result like that url, count of test1 occurences, count of test2, count of test3 like this

it gives me count of all row, i need count of where type is 1, type is 2, type is 3.

SELECT url, count(type) AS count FROM table AS table GROUP BY type

table.findAll({
        where: {

        },
        attributes: ['url', 
            [db.fn('count', db.col('type')), 'count']
        ], 
        group: ['type'],
        // order: [['created_at', 'DESC']]
    })

I need output as 

 [
       {
           url: 'abc.com',
           test1_count: 3,
           test2_count: 5,
           test3_count: 8,
       }
    ]

Upvotes: 0

Views: 50

Answers (1)

Pivot John
Pivot John

Reputation: 1

SELECT url, count(1) AS concat(type, ‘_count’)
FROM table 
GROUP BY url, type;

Upvotes: 0

Related Questions