Author |
Topic |
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-04-22 : 10:52:12
|
I have 2 table (Table1 and Table2)Table1 look like thisID, Company(1,'Company1')(1,'Company1')Table2 look like thisCompany, Date, Bill('Company1', 2013-01-10, 1000)('Company1', 2013-03-12, 1200)('Company2', 2013-01-11, 1300)('Company1', 2013-01-12, 1800)('Company2', 2013-02-10, 2000)('Company2', 2013-01-15, 2300)('Company1', 2013-01-22, 1100)('Company2', 2013-03-29, 3300)('Company1', 2013-02-07, 3100)and when I join that tables it's look like this('Company1', 2013-01-10, 1000)('Company1', 2013-01-12, 1800)('Company1', 2013-01-22, 1100)('Company2', 2013-01-11, 1300)('Company2', 2013-01-15, 2300)I selected only january AND I need to calculate SUM for Company's like this('Company1',3900)('Company2',3600) |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-22 : 11:09:51
|
[code]select t1.Company, SUM(t2.Bill) as Totalfrom Table1 t1 left join Table2 t2 on t1.Company = t2.Companygroup by t1.Companyorder by t1.Company[/code]You can use an INNER JOIN instead of LEFT JOIN if you don't want to include companies that are in Table1, but don't have any row in Table2. |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-04-22 : 11:10:14
|
First querySELECT tb2.* FROM tb2 INNER JOIN tb1 ON tb2.company = tb1.company order by 1,2 second querySELECT tb2.company, sum(bill) as tbill FROM tb2 INNER JOIN tb1 ON tb2.company = tb1.company where month(tb2.[date]) = 1group by tb2.company order by 1 Please look up what was done here.djj |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-04-22 : 12:26:49
|
i got errorcolumn '' is invalid in the select list because is it not contained in either an aggregate function or the GROUP BY clause |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-04-22 : 12:28:47
|
Please show your codedjj |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-04-22 : 15:30:08
|
select s.NAZIV as Firma, p.DATUM, p.ZAPLACILO as Zbir from SUBJEKT s left join PROMET p on s.NAZIV=p.PREJEMNIKwhere p.POSLDOG='3000' and (month(DATUM) = 1 and year(DATUM) = 2013) group by s.NAZIV, p.DATUM, p.ZAPLACILO order by s.NAZIV |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-04-22 : 15:31:56
|
this real table have a lot columns but me only need some.Also column s.NAZIV and p.PREJEMNIK are same. It is a names of companys |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-23 : 01:12:55
|
Above query doesn't have any issues.. show us the complete code.. Rule: Any non-aggregate columns in the SELECT must be exist on GROUP BY clause--Chandu |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-04-23 : 12:37:42
|
That is complete code...this is complete error...Column 'PROMET.ZAPLACILO' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-23 : 13:03:30
|
Strange if that is a complete code and coming up with this error. Anyhow, why are you grouping when there is no aggregates in the Select statement??? just to remove duplicates? If, so why don't you simply use Distinct??CheersMIK |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-04-23 : 13:09:37
|
I try but somethin doesnt work.. |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-23 : 13:18:05
|
Execute the query without Group By clause... and let us know if it runs successfully? SELECT s.NAZIV as Firma,p.DATUM,p.ZAPLACILO as ZbirFROM SUBJEKT s left join PROMET p on s.NAZIV=p.PREJEMNIKwhere p.POSLDOG='3000' and (month(DATUM) = 1 and year(DATUM) = 2013)If it does, then run it with Distinct, as followingSELECT Distincts.NAZIV as Firma,p.DATUM,p.ZAPLACILO as ZbirFROM SUBJEKT s left join PROMET p on s.NAZIV=p.PREJEMNIKwhere p.POSLDOG='3000' and (month(DATUM) = 1 and year(DATUM) = 2013)If number of records for both queries are same then you can use the first query, else grouping in your query is actually meant for restricting the duplication - which can be accomplished by using Distinct In case of any error, please provide the full error message along with the query which you've executed.CheersMIK |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-04-23 : 13:35:35
|
No errors but that is not what I looking for... You can see in my 1 post what i try to cacluatae. Sum of some rows by date... |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-04-23 : 13:50:55
|
Is something like this what you are looking for?select s.NAZIV as Firma, SUM(p.ZAPLACILO) as Zbirfrom SUBJEKT s left join PROMET p on s.NAZIV=p.PREJEMNIKwhere p.POSLDOG='3000' and (month(DATUM) = 1 and year(DATUM) = 2013)group by s.NAZIV,order by s.NAZIV djj |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-23 : 13:53:05
|
DATUM seems to store date informationNAZIV and ZAPLACILO stores company name. then which table's field is storing the bill information? and that field is in SUBJEKT or PROMET??The resulting query would somehow be as followingSELECT NAZIV,ZAPLACILO, Sum(ISNULL(<TableAlias.FieldNameStoringBillInformation>,0)) SumTotalFROM <your table joins logic should goes in here and the where condition as well>GROUP BY NAZIV,ZAPLACILO -- these are the two fields (non-aggregate columns) that should only be in the group by clause, sum(bill) is acually an aggregate columnCheersMIK |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-04-23 : 14:10:38
|
select p.PREJEMNIK, sum (p.ZAPLACILO) as SumaJanuar from PROMET p left join SUBJEKT son s.NAZIV=p.PREJEMNIKwhere p.POSLDOG='3000'and (month(DATUM) = 1 and year(DATUM) = 2013)group by p.PREJEMNIKorder by p.PREJEMNIKThis is OK. I calculate for January. And how to calculate in same query for February, March and sum(janury + february + march ) |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-04-23 : 14:16:01
|
ZAPLACILO is billNAZIV is Company from table SUBJEKTPREJEMNIK is Company from table PROMET |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-23 : 14:16:08
|
and (month(DATUM) = 2 and year(DATUM) = 2013) -- Feb 2013and (month(DATUM) = 3 and year(DATUM) = 2013) -- Mar 2013and (month(DATUM) = 4 and year(DATUM) = 2013) -- Apr 2013....and so onCheersMIK |
|
|
nikoz
Yak Posting Veteran
63 Posts |
Posted - 2013-04-23 : 14:20:19
|
Yes like this... and (month(DATUM) = 1 and year(DATUM) = 2013) -- Jan 2013and (month(DATUM) = 2 and year(DATUM) = 2013) -- Feb 2013and (month(DATUM) = 3 and year(DATUM) = 2013) -- Mar 2013.. |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-23 : 14:20:21
|
quote: Originally posted by nikozThis is OK. I calculate for January. And how to calculate in same query for February, March
Infact, for Feb replace and (month(DATUM) = 1 and year(DATUM) = 2013) condition with,and (month(DATUM) = 2 and year(DATUM) = 2013) -- Feb 2013and for Mar replace with and (month(DATUM) = 3 and year(DATUM) = 2013) -- Mar 2013quote: and sum(janury + february + march )
i think this is what you want to use at the bottom of some report? if thats the case, do it on the application/report designer levelCheersMIK |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-23 : 14:24:06
|
This is how you can have "company and month" wise sum for the Jan, Feb, and March. select p.PREJEMNIK, month(DATUM), sum (p.ZAPLACILO) as SumaJanuar from PROMET p left join SUBJEKT son s.NAZIV=p.PREJEMNIKwhere p.POSLDOG='3000'and (month(DATUM) in(1,2,3) and year(DATUM) = 2013)group by p.PREJEMNIK, month(DATUM)order by p.PREJEMNIKCheersMIK |
|
|
Next Page
|