Counting first table in joined tables

Question

I have problem in my sql queries. I have 2 tables (a. Sales, b. SalesDetail). With rows:

Sales                  SalesDetail
------                 -----------
SaleNo  : MakeDate     IDSalesDetail: SaleNo : ItemCode : ItemPrice : ItemQty
--------:---------     -------------:--------:----------:-----------:--------:      
    1   :12/18/2015         1       :   1    :   001    :   100     :   5
    2   :12/28/2015         2       :   2    :   001    :   100     :  10
    3   :01/08/2016         3       :   3    :   001    :   100     :  20
                            4       :   2    :   002    :    50     :  10
                            5       :   1    :   002    :    50     :   5

I need query that provides results like

TotalReceipt : Year  : Amount :
-------------:-------:--------:
      2      : 2015  : 2250   :
      1      : 2016  : 2000   :

I used this query

SELECT COUNT(Sales.SaleNo) AS TotalReceipt, 
       FORMAT(Sales.MakeDate, 'yyyy') AS [Year], 
       SUM(SalesDetail.ItemQty * SalesDetail.ItemPrice) AS Amount
FROM (Sales INNER JOIN SalesDetail ON Sales.SaleNo = SalesDetail.SaleNo)
GROUP BY FORMAT(Sales.MakeDate, 'yyyy')

But it gives me this instead

TotalReceipt : Year  : Amount :
-------------:-------:--------:
      4      : 2015  : 2250   :
      1      : 2016  : 2000   :

Since the Count(*) affects SalesDetail table.

If anyone can solve my problem, I'd be grateful

Thankyou, Have a nice day!

score 0 · Answer 1 · answered Dec 28 '15 at 06:40

0

Try using distinct while counting like:

SELECT COUNT(DISTINCT SalesDetail.NoSale) AS TotalReceipt, 
FORMAT(Sales.MakeDate, 'yyyy') AS [Year], 
SUM(SalesDetail.ItemQty * SalesDetail.ItemPrice) AS Amount
FROM (Sales INNER JOIN SalesDetail ON Sales.SaleNo = SalesDetail.SaleNo)
GROUP BY FORMAT(Sales.MakeDate, 'yyyy')

answered Dec 28 '15 at 06:40

SMA

36,381
8
49
73

Syntax error (missing operator) in query expression 'COUNT(DISTINCT SalesDetail.NoSale)'. I'm using Access 2007 FYI – Fura Dec 28 '15 at 06:55

score 0 · Accepted Answer · answered Dec 28 '15 at 08:18

Just found the formula for my own answer by myself, If you are wondering how please check this

SELECT COUNT(*) AS TotalReceipt, Format(MakeDate, 'yyyy') AS [Year], SUM(Total) AS Amount
FROM (        
             SELECT  SaleNo, SUM(ItemQty * ItemPrice) AS Total
                      FROM            SalesDetail
                      GROUP BY SaleNo) DetailSum 
INNER JOIN Sales ON Sales.SaleNo = DetailSum.SaleNo
GROUP BY Format(MakeDate, 'yyyy')

Counting first table in joined tables

2 Answers2