SQL Server: Sum(column) and count(column) in Select and Join statement

Question

I'm trying to query how many transaction each loyaltyID column has in AnthemTxns_Jr and what the total of all of their transactions adds up to. Right now, when I run this script, it says,

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.AnthemTxns_JR.count", or the name is ambiguous

Code:

Select DISTINCT 
   dbo.ANTHEM_IDS_JR.loyaltyID,   
   dbo.ANTHEM_IDS_JR.Military_Type,,
   dbo.ANTHEM_IDS_JR.Military_Date, 
   dbo.AnthemTxns_JR.count(CheckTotal), 
   dbo.AnthemTxns_JR.sum(CheckTotal)
From 
   dbo.ANTHEM_IDS_JR
JOIN 
   dbo.AnthemTxns_JR ON dbo.ANTHEM_IDS_JR.loyaltyID = dbo.AnthemTxns_JR.loyaltyID
WHERE  
   Military_Type = 'Active Duty' 
   AND ACTIVE = 1 
   AND datalength(dbo.ANTHEM_IDS_JR.Military_Date) > 0 
ORDER BY 
   loyaltyID;

You need a group by clause to associate the functions count/sum. — PeonProgrammer, Jun 09 '14 at 19:11
Could you elaborate more on that please? I inserted `GROUP BY dbo.ANTHEM_IDS_JR.loyaltyID` at the end of my script, and I still get an error message — pbars23, Jun 09 '14 at 19:22

Tanner_Gram · Accepted Answer · 2014-06-09T19:24:03.300

0

Try this... It looks like you have it a bit mixed up in the select statement

Select DISTINCT 
       dbo.ANTHEM_IDS_JR.loyaltyID,   
       dbo.ANTHEM_IDS_JR.Military_Type,
       dbo.ANTHEM_IDS_JR.Military_Date, 
       count(dbo.AnthemTxns_JR.CheckTotal) as CheckTotalCount, 
       sum(dbo.AnthemTxns_JR.CheckTotal) as CheckTotalSum
    From 
       dbo.ANTHEM_IDS_JR
    JOIN 
       dbo.AnthemTxns_JR ON dbo.ANTHEM_IDS_JR.loyaltyID = dbo.AnthemTxns_JR.loyaltyID
    WHERE  
       Military_Type = 'Active Duty' 
       AND ACTIVE = 1 
       AND datalength(dbo.ANTHEM_IDS_JR.Military_Date) > 0 
    Group by 
    dbo.ANTHEM_IDS_JR.loyaltyID, 
    dbo.ANTHEM_IDS_JR.Military_Type,    
    dbo.ANTHEM_IDS_JR.Military_Date  
    ORDER BY 
       loyaltyID;

edited Jun 09 '14 at 19:24

answered Jun 09 '14 at 19:12

Tanner_Gram

1,090
9
19

i get an error when I run this. The error says, 'Msg 8120, Level 16, State 1, Line 68 Column 'dbo.ANTHEM_IDS_JR.Military_Type' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause'. I believe I need a GROUP BY clause, but I don't know which columns should be grouped. – pbars23 Jun 09 '14 at 19:20
I made an edit. Try it again, and see what it does. Here is a link to explain why I added the group by to the new code. http://www.w3schools.com/sql/sql_groupby.asp – Tanner_Gram Jun 09 '14 at 19:24
It worked! Thanks so much! I'd hate to bother you for more info, but could you explain why you chose `GROUP BY` for the 3 columns: `dbo.ANTHEM_IDS_JR.loyaltyID,dbo.ANTHEM_IDS_JR.Military_Type,dbo.ANTHEM_IDS_JR.Military_Date` please? – pbars23 Jun 09 '14 at 19:27
You have to include the non-aggregates in the group by. Read that article I posted for further elaboration on it. This Stack forum article goes into the depth you should need. = ) http://stackoverflow.com/questions/3168042/can-i-use-non-aggregate-columns-with-group-by – Tanner_Gram Jun 09 '14 at 19:29

SQL Server: Sum(column) and count(column) in Select and Join statement

1 Answers1