SQL Server : function SUM and data from more tables

Question

I have these tables:

Hraci:

IDHrace (int)
Jmeno (varchar)

Ucast_Zapas:

ID_Hrace_zapas (int)
branky (int)

I want SUM how much "branky" WHERE ID_Hrace_zapas=IDHrace and Jmeno="karel" for example...

This is my code:

SELECT 
    SUM(Ucast_Zapas.Branky), Ucast_Zapas.ID_Hrace_zapas, Hraci.Jmeno,Hraci.IDHrace
FROM 
    Hraci, Ucast_Zapas  
WHERE 
    (Ucast_Zapas.ID_Hrace_zapas=Hraci.IDHrace) AND (Hraci.Jmeno='smajlik');

I don´t know, where is the problem, SQl writes this error:

Column 'Ucast_Zapas.ID_Hrace_zapas' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

rule of thumb: when using an aggregate function in SQL, always use GROUP BY on the non aggregated columns (aka, the plain ones). In this case, you would most likely need to skip selecting the ID because then you could leave the SUM() function out of play anyhow — Najzero
– Najzero, Commented Mar 27, 2013 at 10:31
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was discontinued with the ANSI-92 SQL Standard (more than 20 years ago) — marc_s
– marc_s, Commented Mar 27, 2013 at 10:32

Nenad Zivkovic · Accepted Answer · 2013-03-27 10:41:12Z

1

SELECT IDHrace, SUM(branky)
FROM Hraci
INNER JOIN Ucast_Zapas ON Ucast_Zapas.ID_Hrace_zapas=Hraci.IDHrace
WHERE Hraci.Jmeno='smajlik'
GROUP BY IDHrace

answered Mar 27, 2013 at 10:41

Nenad Zivkovic

18.6k6 gold badges45 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : function SUM and data from more tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related