SQL查询计数不同 [英] SQL Query to Count Distinct
问题描述
大家好,
我需要你的帮助来统计Totals中的不同。
I would need your help to count distinct in Totals.
问题陈述:
发票数据。
为了简化问题,让我们假设一个国家/地区每天只生成1张发票并且有2件物品。
to simplify the problem let us assume a Country had only 1 invoice generated for a day and it had 2 Items.
1来自食品类别,1来自ALC类别。
1 from Food Category and 1 from ALC category.
当我在国家级别(按国家/地区)编写发票计数时,我得到1个发票,但是当我在组中添加类别时通过它在ALC中给出1,在FOOD中给出1是正确的。我需要的是ALC 1和FOD 1,总ROW 1,因为总数在国家
级别,数字应匹配,不论级别
When I write count of Invoice at Country level (group by Country) I get 1 Invoice but when I add Category in group by it gives 1 in ALC and 1 in FOOD which is correct. What I need is 1 for ALC and 1 for FOD and a Total ROW with 1 since total is at Country level and number should match irrespective of Levels
AJ
推荐答案
请检查这是否有帮助
Please check if this helps
CREATE
TABLE #Invoice
( Country
VARCHAR ( 100 ),
InvoiceNumber INT )
CREATE TABLE #Invoice (Country VARCHAR(100), InvoiceNumber INT)
INSERT
INTO #Invoice
VALUES
( 'India' , 1 )
INSERT INTO #Invoice VALUES ('India',1)
CREATE
TABLE #InvoiceCat
( InvoiceNumber
INT , Category
VARCHAr ( 100 ))
CREATE TABLE #InvoiceCat (InvoiceNumber INT, Category VARCHAr(100))
INSERT
INTO #InvoiceCat
VALUES
( 1 , 'Food' )
INSERT INTO #InvoiceCat VALUES (1,'Food')
INSERT
INTO #InvoiceCat
VALUES
( 1 , 'All' )
INSERT INTO #InvoiceCat VALUES (1,'All')
SELECT
a 。 国家 ,
COUNT ( DISTINCT
a 。 InvoiceNumber )
AS InvoiceCount ,
COUNT(DISTINCT a.InvoiceNumber) AS InvoiceCount,
b 。 类别 ,
COUNT ( b 。 类别
< span style ="font-size:9.5pt; font-family:Consolas; color:grey">)
AS CategoryCount
COUNT(b.Category ) AS CategoryCount
FROM #Invoice a
INNEr
JOIN #InvoiceCat b
ON a 。 InvoiceNumber
= b 。 InvoiceNumber
GROUP
BY a 。 国家 ,
b 。 类别
这篇关于SQL查询计数不同的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!