CODEWITHSUNDEEP
sql
Freddakos
Freddakos

Reputation: 107

SQL query syntax with multiple SUM

I have the following sql query:

SELECT
    dbo.Stores.Store_Name,
    SUM(tziros_imeras) AS tziros_imeras,
    SUM(tziros_imeras_X_fpa) AS tziros_imeras_X_fpa,
    SUM(polithenta_karotsia) AS polithenta_karotsia,
    SUM(polithenta_kathismata) AS polithenta_kathismata,
    SUM(ask_for_furni) AS ask_for_furni,
    SUM(ask_for_karotsi) AS ask_for_karotsi,
    SUM(ask_for_kathisma) AS ask_for_kathisma,
    SUM(furni_reservation) AS furni_reservation,
    SUM(tziros_imeras) AS tziros_imeras
FROM
    dbo.Reports,
    dbo.Stores
WHERE
    dbo.Reports.Store_id = dbo.Stores.Store_id
GROUP BY 
    dbo.Stores.Store_Name
ORDER BY 
    dbo.Stores.Store_Name ASC

This query works perfect and he does what I am really needed to do (I am getting the desired results in the loop).

My problem is that I need one more info to be included in my results. I want the info of the column nomisma from the table Nomismata.

The tables that involved to do that are the following 2: Stores & Nomismata and they have the following structure:

Is there any possible way to have the nomisma info to be included correctly in my query??

Upvotes: 0

Views: 52

Answers (3)

Idoneus
Idoneus

Reputation: 86

First of all, I recommend to use "JOIN" (either INNER, LEFT, RIGHT ... as you need) within the "FROM" clause from: 

FROM
    dbo.Reports,
    dbo.Stores
WHERE
    dbo.Reports.Store_id = dbo.Stores.Store_id
GROUP BY 
    dbo.Stores.Store_Name
ORDER BY 
    dbo.Stores.Store_Name ASC

to 

FROM
    dbo.Reports  
INNER JOIN 
    dbo.Stores ON dbo.Reports.Store_id = dbo.Stores.Store_id
GROUP BY 
    dbo.Stores.Store_Name
ORDER BY 
    dbo.Stores.Store_Name ASC

... now, including your new table:

SELECT
    dbo.Stores.Store_Name, 
    Nomismata.nomisma,
    SUM(tziros_imeras) AS tziros_imeras,
    SUM(tziros_imeras_X_fpa) AS tziros_imeras_X_fpa,
    SUM(polithenta_karotsia) AS polithenta_karotsia,
    SUM(polithenta_kathismata) AS polithenta_kathismata,
    SUM(ask_for_furni) AS ask_for_furni,
    SUM(ask_for_karotsi) AS ask_for_karotsi,
    SUM(ask_for_kathisma) AS ask_for_kathisma,
    SUM(furni_reservation) AS furni_reservation,
    SUM(tziros_imeras) AS tziros_imeras
FROM    
    dbo.Reports  
INNER JOIN 
    dbo.Stores ON dbo.Reports.Store_id = dbo.Stores.Store_id
INNER JOIN 
    Nomismata ON dbo.Stores.nomisma_id = Nomismata.nomisma_id
GROUP BY 
    dbo.Stores.Store_Name, Nomismata.nomisma
ORDER BY 
    dbo.Stores.Store_Name ASC

Enjoy!

Upvotes: 4

ying lam
ying lam

Reputation: 1

join Nomismata to the existing query

SELECT
Stores.Store_Name,
Nomismata.nomisma,
SUM(tziros_imeras) AS tziros_imeras,
SUM(tziros_imeras_X_fpa) AS tziros_imeras_X_fpa,
SUM(polithenta_karotsia) AS polithenta_karotsia,
SUM(polithenta_kathismata) AS polithenta_kathismata,
SUM(ask_for_furni) AS ask_for_furni,
SUM(ask_for_karotsi) AS ask_for_karotsi,
SUM(ask_for_kathisma) AS ask_for_kathisma,
SUM(furni_reservation) AS furni_reservation,
SUM(tziros_imeras) AS tziros_imeras
FROM dbo.Reports Reports
INNER JOIN dbo.Stores Stores
ON Reports.Store_id = Stores.Store_id
JOIN dbo.Nomismata Nomismata
ON Nomismata.nomisma_id = Stores.nomisma_id
GROUP BY Stores.Store_Name,
Nomismata.nomisma
ORDER BY Stores.Store_Name

Upvotes: 0

Vash
Vash

Reputation: 1787

You can add a join with the Nomismata table(with Stores table) on nomisma_id column. Add the nomisma column in the SELECT and GROUP BY clauses. Since the joining column is nomisma_id, I am assuming nomisma is a dimension and not a numeric column to be summed.

Also added JOIN instead of cross joins that you had. This would make the query more efficient. 

SELECT
dbo.Stores.Store_Name, dbo.Nomismata.nomisma,
SUM(tziros_imeras) AS tziros_imeras,
SUM(tziros_imeras_X_fpa) AS tziros_imeras_X_fpa,
SUM(polithenta_karotsia) AS polithenta_karotsia,
SUM(polithenta_kathismata) AS polithenta_kathismata,
SUM(ask_for_furni) AS ask_for_furni,
SUM(ask_for_karotsi) AS ask_for_karotsi,
SUM(ask_for_kathisma) AS ask_for_kathisma,
SUM(furni_reservation) AS furni_reservation,
SUM(tziros_imeras) AS tziros_imeras
FROM
dbo.Reports 
JOIN dbo.Stores ON dbo.Reports.Store_id = dbo.Stores.Store_id
JOIN dbo.Nomismata ON dbo.Nomismata.nomisma_id = dbo.Stores.nomisma_id
GROUP BY dbo.Stores.Store_Name, dbo.Nomismata.nomisma
ORDER BY dbo.Stores.Store_Name ASC;

Upvotes: 1

Related Questions