根据其他列的条件拆分列

发布于 2025-01-11 05:06:23 字数 427 浏览 2 评论 0原文

我正在尝试形成一个表来计算 sql 中的用户保留率。到目前为止我所做的是这样的：

SELECT date AS 'DAY', COUNT(userid) AS 'TOTAL LOGINS'
FROM dbo.login
GROUP BY date

作为回报，我得到了这个表：

在此处输入图像描述

但是，总共有一些用户登录栏有ios，有些有android。我想在表中添加两列以显示 ios 登录和 android 登录（这两列的总和将等于每行的总登录列）。

我的原始表（dbo.login）有一个名为 platform 的列，显示每个用户的平台。但我无法编写查询来显示 ios 用户或特别是 android 用户的数量。你能帮助我吗？

原文

I am trying to form a table to calculate user retention in sql. What I did so far was this:

SELECT date AS 'DAY', COUNT(userid) AS 'TOTAL LOGINS'
FROM dbo.login
GROUP BY date

In return I get this table:

enter image description here

However, some users in the total logins column have ios and some have androids. I want to add two extra columns to my table to show ios logins and android logins (sum of this two columns will be equal to total logins column for each row).

My original table (dbo.login) has a column named platform that displays every users platform. But I could not write a query to show the count of ios users or androis users in particular. Can you help me?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

碍人泪离人颜 2025-01-18 05:06:23

用这个

SELECT DATE AS 'DAY',COUNT(USERID) AS 'TOTAL LOGINS',
SUM(CASE WHEN [PLATFORM] = 'ANDROIDS' THEN 1 ELSE 0 END) AS ANDROIDS,
SUM(CASE WHEN [PLATFORM] = 'ISO' THEN 1 ELSE 0 END) AS ISO
FROM 
DBO.LOGIN GROUP BY DATE

use this

SELECT DATE AS 'DAY',COUNT(USERID) AS 'TOTAL LOGINS',
SUM(CASE WHEN [PLATFORM] = 'ANDROIDS' THEN 1 ELSE 0 END) AS ANDROIDS,
SUM(CASE WHEN [PLATFORM] = 'ISO' THEN 1 ELSE 0 END) AS ISO
FROM 
DBO.LOGIN GROUP BY DATE

回复收藏 0 原文

~没有更多了~