我们有一组来自SQL查询的数据,我们希望使用这些数据创建一个线条图。
SELECT REPLACE(req2.value,"Sprint 1", "sum") AS sprint , SUM(val) AS VALUE FROM
(SELECT issue AS iss,SUM(VALUE) AS val FROM dataissue,issue WHERE FIELD LIKE 'poin%' AND dataissue.issue = issue.id AND VALUE IS NOT NULL AND issue IN ( SEL
我有一个用于检索所有项的SQL。
SELECT A.item_code,A.description,A.uom, A.open_stock, B.recd_total, C.issue_qty
FROM chem_master as A
LEFT JOIN ( SELECT item_code, sum(recd_qty) as recd_total
from chem_reciepts GROUP BY item_code) as B on A.item_code=B.item_code
LEFT JOIN(SELECT item_code, sum(iss_qty) as issue_qt
与这个proc sql相当的数据步长是什么?
proc sql;
create table issues2 as(
select request,
area,
sum(issue_count) as issue_count,
sum(resolved_count) as resolved_count
from
issues1
group by request, area
);
SELECT (
(SUM(t_price) - SUM(a_dvpay)) - (
SELECT SUM(inst_amount)
FROM installment
WHERE uid = user_info.uid
)
) AS remaining
FROM user_info
WHERE faculty_id = @faculty_id
GROUP BY uid;
此SQL查询返回多行中的剩余结果。我想将剩余值之和为总计剩余。
SQL错误(1055):Select的表达式#7没有按子句分组,并包含非聚合列'test_db.pid.product_id‘,它在功能上不依赖于group子句中的列;这与sql_mode=only_full_group_by不完整
SELECT pro.name AS product_name,pro.id AS product_id,pro.model AS product_model,bnd.name AS brand_name,sub_cat.name AS sub_category_name,cat.name AS category_name,
IFNULL(sum(pi
有没有人可以建议如何编写一个SQL查询来包含多行中多个字段的总和。我使用的是下面的查询,但是它总是说“select行中的字段是无效的,因为它既没有包含在聚合函数中,也没有包含在GROUP BY子句中。”
Select ClaimId,InternalICN,BilledAmt,
Sum(PayAmt) as TotPayAmt,Sum(COBAmt) as TotCOBAmt,Sum(PrePayAmt) as
TotPrePayAmt
from CAIDEnc.IntEncTracking.EncounterList
这是我的sql
SELECT A.item_code,A.description,A.uom, A.open_stock, B.recd_total, C.issue_qty
FROM chem_master as A
LEFT JOIN (
SELECT item_code, SUM(recd_qty) as recd_total
FROM chem_receipts
GROUP BY item_code) AS B
ON A.
我试图选择上个月的期末余额,并将其作为下个月的期初余额,但我似乎没有得到正确的结果。
我已经使用此代码来选择其他列并获得平衡:
SELECT Year1,StockNumber,InventoryName,Month1,
SUM(ABS(RECEIVE_FROM_PO)) AS RPO,SUM(ABS(RECEIVE_XFER))AS XEFR,
SUM(ABS(RECEIVE_ADJ)) as RDJ,SUM(ABS(RETURN1)) AS RET,
SUM(ABS(TRANSFER)) as TSFR,SUM(ABS(ISSUE)) as
我有一个表格,列如下:
customerID -具有相同ID的多行的客户ID
actionVal -0或1
我想数一下有多少客户(customerID)有超过一行(actionVal)等于1
我想出了这个SQL语句却没有运气..。
SELECT customerID, SUM(actionVal), COUNT(DISTINCT customerID) as total_C
FROM table1
GROUP BY customerID
HAVING SUM(actionVal) > 1
我要找的结果是“total_C”
我的工作表,表名: sales
这里是我的表,sl_no是主键表结构:
CREATE TABLE SALES
( SL_NO NUMBER PRIMARY KEY, REGION VARCHAR2(10) NOT NULL,
MONTH VARCHAR2(20) NOT NULL, YEAR NUMBER NOT NULL,
SALES_AMOUNT NUMBER NOT NULL )
,这里是表数据:
SQL> select * from sales;
SL_NO REGION MONTH YEA
我有一个插入了数据标题和数据日期的表。现在,我想要做的就是计算统计数据。
我可以在一条sql语句中执行多个计数吗?像from,date列,我想要计算这个月有多少,今年有多少,直到选择了月份。有可能吗?如果是,是如何实现的?
这就是我目前所想出来的。
SELECT a.trigger_type_code
, c.trigger_name
, COUNT(*) AS number
FROM issue_trigger a
INNER JOIN cs_issue b
ON b.issue_id = a.issue_id
INNER JOIN ref_trigge
我在雪花中使用下面的SQL,无法得到实例的计数。我想要达到的最终目标是,我想知道有同样问题的团队和实例的数量。
我的SQL查询:
SELECT
priority,
QID,
issue,
solution,
team,
instance,
COUNT(team) AS TEAM_COUNT,
SUM(instance) AS Instance_count
FROM
ABCDTABLE
WHERE
priority != 'NONE'
GROUP_BY
priority, QID, i
你好,我有这样的情况,我基本上需要为这样的语句编写SQL代码。
select *,
case when 'Issue' IN ('Overforecasted', 'Underforecasted') AND 'Start Date' DISTINCT 3 dates THEN 'Issue exists for 3 weeks in a row'
FROM Merged;
我知道这不是一种适当的SQL格式,但是有人知道如何编辑它吗?
每一个DMDUNIT检查在列“Per”中是否有3个问题,稍后检查
我试图分组两次,一次按单个级别字段,然后按时间戳字段的月份进行分组。
对SQL并不陌生,但下面是我在阅读另一篇文章之后想到的:
SELECT
VwNIMUserDim.USER_EMAIL_ADDRESS,
VwNIMEventFct.NIM_USER_ID,
SUM(CASE WHEN NIM_EVENT_TYPE_ID = 880 THEN 1 ELSE 0 END) AS APP_OPEN,
SUM(CASE WHEN NIM_EVENT_TYPE_ID = 881 THEN 1 ELSE 0 END) AS AUT
,如果增加和减少,我怎么能把数量相加?
表 Product_stock
sql语句
SELECT SUM(product_stock_quantity) AS total From product_stock GROUP BY product_stock_status
我希望结果是
共计100-50 = 50
当多行记录时,如果增加和减少quantity.Please,我不知道要对数量进行求和。
我在这里写了我的第一个帖子,因为我一直无法找到自己的解决方案。我对编程/sql/ Oracle知之甚少,但我偶尔会使用(我相信)Oracle数据库。
我想要完成的是获取一个查询结果,并为每个COUNT()提供自己的列。这样做的原因是要为web应用程序生成报告。我需要结果填充它们自己的列,以便为报表设计器解析出字段。
我的查询如下所示:
SELECT ISSUE, COUNT(ISSUE) AS ISSUE_COUNT FROM ISSUE_TABLE WHERE TIME >TO_DATE('08/10/2017 00:00:00','MM/DD/YYYY HH
我有两张桌子:Issue和Return。这两个表都有列article_id、person_id和quantity。我需要从一个人的Return表中减去Issue表,并根据文章名对结果进行分组。
第一个选择如下所示:
SELECT Article.Name, SUM(Issue.Quantity)
FROM Issue LEFT JOIN Article ON (Issue.ArticleID = Article.ID)
WHERE Issue.PersonID = 2
GROUP BY Article.Name
ArticleName数量
第二,像这样:
SELECT Article.Na
我试图加入两个不同的查询。每个查询单独工作,但加入时不起作用。
SELECT *
FROM (
SELECT i.id AS wid, i.start_time, i.end_time, i.drawing_time, i.result, SUM( t.amount ) AS wpAmount, SUM( t.prize ) , COUNT( t.id ) AS cntWinTickets
FROM issues i
LEFT JOIN orders o ON o.issue_id = i.id
LEFT JOIN tickets
我有一个а的小问题,写通过Activerecord查询接口它实际上是:
Gp.select("date('gps'.'created_at') as date,('users'.'name') as name, SUM('gps'.'sum_issue') as sum_issue").joins('LEFT JOIN users ON users.id = gps.user_id').where("users.ab_id = :abs_id AND u
我有以下查询
Select
Date, Item_Code,
SUM(In_Quantity) as In_Quantity,
SUM(Issue_Quantity) as Issue_Quantity,
(SUM(In_Quantity) - SUM(issue_Quantity)) as BalanceQty
from
(select
tbl_add_product.Date as Date, tbl_add_product.Item_Code,
tbl_add_product.In_Quantity, 0
我有这样一个SQL语句
SELECT
pa.col1,
SUM(ps.col2) col2,
SUM(psl.col2) col2_previous_month
FROM
pa
LEFT JOIN
ps ON pa.Id = ps.Id AND ps.date = @currDate
LEFT JOIN
ps as psl ON psl.Id = ps.Id AND psl.date = dateadd(month, - 1, @currDate)
GROUP BY
pa.col1;
这个SQL经常被调用,而且由于表ps有1
下面的查询失败并抱怨#1111 - Invalid use of group function,因为两个SUM()调用都嵌入在IF循环中。到目前为止,我正试图在迁移到另一个SQL查询时实现相同的目标,但我遇到了困难,有谁能为我指出正确的方向呢?
数据库由一个invoices表组成,如果客户没有立即支付发票,而是通过安装付款,则该表可能具有一些invoices_payments。知道了这一点,我尝试求和所有invoices.invoice_total减去每个不等于当前周的invoices_payments.payment_date,只有当invoices.issued_date等于当前周时,如果
我有以下代码,但是我的mysql查询没有返回任何值。请告诉我我做错了什么:
foreach($stagearray as $catgry){
echo $catgry;//this is returning the values
$sql = mysql_query("
select
count(reference) as CCOUNTS,
assignee_group
from
issue_tracking_issues i,
issue_t
如何将以前的行值和当前行值相加到一个新列?从这个查询中:
SELECT
A.DATE,
A.SHIFT,
A.RECEIPT,
A.ISSUE
FROM
(SELECT
B.DATE,
B.SHIFT,
CASE WHEN B.JUDUL='ISSUE' THEN B.QTY END AS ISSUE,
CASE WHEN B.JUDUL='RECEIP
我的SQL查询返回一个错误#1111 -在对结果进行行求和时,组函数的使用无效。
此查询工作正常,并返回许多行。
SELECT credit.Amount - SUM( COALESCE( creditreceived.Amount, 0 ) ) AS
AmountReceivable FROM credit LEFT JOIN creditreceived ON credit.ID =
creditreceived.CreditID WHERE credit.CompanyID = '1' GROUP BY credit.id
结果图像:
我想把这些行和起来,所以我使用
SELECT
SUM(fabricinventory.sqty) AS shipped,
SUM(fabricinventory.rcvd) AS received
FROM
(SELECT
SUM(requisition.issue) AS issue
FROM
requisition
INNER JOIN
fabricinventory ON fabricinventory.id = requisition.fab_id
GROUP BY
fabricinvent
我在做一个仓库项目。在这个项目中,我需要得到一个报告的输出,它将总结每月发布(出售)的项目。输出是每个期望从下面的MySQL查询。
SELECT Product,Stock,SUM(CASE WHEN MONTH(Issue_Date) = 1 THEN `Issue_Qty` END) jan, SUM(CASE WHEN MONTH(Issue_Date) = 2 THEN `Issue_Qty` END) feb, SUM(CASE WHEN MONTH(Issue_Date) = 3 THEN `Issue_Qty` END) mar, -------------- SUM(CASE
select s.part_no
,s.part_name
,s.uom
,i.Invoice_Quantity
,r.Return_Quantity
,p.Issue_Quantity
,sr.Quantity
,rp.Return_Quantity
,er.Quantity
from dbo.dri_Stock as s
left join
(
select part_no,sum(Invoice_Quantity) as Invoice_Quantity
from dbo.dri_InwardEntr
我正在尝试类似这样的东西:
INSERT INTO MyTable (
Col1
,Col2 )
OUTPUT DISTINCT -- issue is with DISTINCT
INSERTED.Col1
,@otherParameter
INTO IdListTable
SELECT ColA
,ColB
,SUM(ImportantNumber)
FROM MyOtherTable
GROUP BY ColA, ColB
除了SQL不希望我在OUTPUT子句中使用DISTINCT。我想到的解决方法
早上好,我的问题是,我正在五旬节创建一个仪表板。我不知道我是否必须使用宾得或sql来解决这个问题。为了能够成为,我更喜欢sql。我有很多行,我展示了我用来做不同的和。我不想再重复这些台词了。sql如下:
SELECT p.pname AS project,
t.pname AS type,
c.cname
有没有人能帮忙。我有两张表,发给员工的项目和员工返回的项目。我已经编写了以下联合查询,不出所料,每个结果都有2行
select count(itemcode) as issued, 0 as returned, issue.employee, issue.itemcode
from issue
group by itemcode, employee
Union
select 0 as issued, count(itemcode) as returned, return.employee, return.itemcode
from return
group by itemcode, e
我正在尝试找出如何使用Linq将以下SQL语句转换为Entity Framework:
SELECT SUM(Column1) AS Correct
, SUM(Column2) AS Incorrect
, UserName
FROM Stats
WHERE (StatType = 0)
GROUP BY UserName
ORDER BY UserName
出于这个问题的目的,数据库中的所有列类型都是INT类型,并且每个用户有多行数据。我基本上想在我的输出3列,为每个用户的正确和不正确的选择总数。
它看起来像是一条简单的SQL语句,但无论我在LinqPad之类的
这是我的代码。有没有人能告诉我是什么地方不对?我已经花了三天多的时间试图弄清楚,但我没有看到它,我的报告将于下周到期。
SELECT
t1.IT1 as "Issue Type",
t1.sum(REC) as "Total Received",
t2.sum(RES) as "Total Resolved",
t1.Count(project_key) as REC,
t2.Count(project_key) as RES
FROM
(select
IT1 as "Issue Type
如何在一个子选择中简单地硬编码多行?
我知道我可以做到():
SELECT x.id, SUM(ISNULL(OtherTable.count_column,0))
FROM (SELECT 12 AS id
UNION
SELECT 21 AS id
UNION
SELECT 101 AS id
/*AND so on */
) AS x
LEFT JOIN OtherTable ON x.id = OtherTable.id
Group BY x.id
有没有一种不那么笨拙和冗长的方法来做到这一点?
我真正想要的是:
SELECT id, SUM(ISNULL(count_c
我正在尝试使用以下脚本计算2010年员工的工作时数。某些员工在同一年的表中可能有多行。虽然如果没有小时数,字段将填充为0,但总结果可能指示为NULL。我使用的是SQL Standard 2005。如有建议,将不胜感激。
SELECT TOP (100) PERCENT id, year, SUM(ISNULL(Reghours_worked, 0)) AS RegHours, SUM(ISNULL(OThours, 0))
OThours, SUM(RegHours + OThours) AS TotalHours_2010
FROM dbo.ho
我正在编写一个应用程序,允许在几个选项中排名(投票)。如果来自互联网的用户(没有在应用程序中签名)投票,我只需将其存储在一个user_id = NULL中,但是如果有一些经过身份验证的用户投票,我会保存他的user_id
我有以下数据结构(字段是问题的最小值)
Issues (What the people is gonna vote about)
------------
id
title
description
Options
------------
id
issue_id
title
description
Votes
------------
id
option_id
user_