Convert SQL Query to Mongo

[rocpm...@gmail.com]

I know this is probably a stupid question, but I have searched all over and can't find a good answer. I am looking to convert this SQL query to a mongo find, any help would be appreciate. I know it will use aggregate but can't figure it out

SELECT AccountNum,
        SUM(CASE WHEN lower(ModemStatus) LIKE '%online%' OR lower(ModemStatus) LIKE '%oper%' THEN 1 ELSE 0 END) Online,
        SUM(CASE WHEN lower(ModemStatus) LIKE '%offline%' THEN 1 ELSE 0 END) Offline,
        SUM(CASE WHEN lower(ModemStatus) NOT LIKE '%online%' AND lower(ModemStatus) NOT LIKE '%oper%' and lower(ModemStatus) NOT LIKE '%offline%' THEN 1 ELSE 0 END) Other
FROM StatTesting
GROUP BY AccountNum

Data returned
AccountNum    Online    Offline    Other
1    3    0    0
2    3    0    0
3    2    1    0
4    0    3    0

[Ivan Grigolon]

Hi,
as a starting guide to map SQL queries into MongoDB you could review the SQL to MongoDB Mapping Chart.

​

Could you please provide below relevant information :

• MongoDB version
• Example source documents in MongoDB collection
• The expected output document
• An example query/aggregation pipeline that you've tried.

Best Regards
Ivan