Group By problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group By problem

Author

Topic

Mari
Starting Member

19 Posts

Posted - 2007-08-30 : 13:01:58

Hi Everyone,

Here I am again, you guys did really help me, and others were quite brilliant to play with it too. Need your help again, still new at sql. How do I do a Group By here? what do you think is wrong with my code? tried different things but couldn't get it to work with the group by including the mandate, and I know it's because it's a column that doesn't exist in any of the tables in the first statement, this is why I gave it the name 'WGEID', but how should I do it? coz the 2nd statement has this column in its tables.It gives error

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Mandate'.

here is my code:

select
[Year],Mandate,
'UA'=SUM(CASE urgent WHEN 'Y' THEN 1 ELSE 0 END),
'AL'=SUM(CASE urgent WHEN 'N' THEN 1 ELSE 0 END),
SUM(CASE when urgent in ('Y','N') THEN 1 ELSE 0 END)as Total_comm
from
#inds i inner join missingperson mp on i.personid=mp.missingpersonid
inner join missingperson_correspondence c on mp.missingpersonid=c.missingpersonid
and i.[month]=month(c.transmitdate) and i.[year]=year(c.transmitdate)
where (((Mandate)='WGEID'))
group by year,Mandate
UNION
select
[Year], Mandate,
'UA'=SUM(CASE CorrespondenceType WHEN 'UA' THEN 1 ELSE 0 END),
'JUA'=SUM(CASE CorrespondenceType WHEN 'JUA' THEN 1 ELSE 0 END),
'AL'=SUM(CASE CorrespondenceType WHEN 'AL' THEN 1 ELSE 0 END),
'JAL'=SUM(CASE CorrespondenceType WHEN 'JAL' THEN 1 ELSE 0 END),
SUM(CASE when CorrespondenceType in ('UA','JAL','JUA','AL') THEN 1 ELSE 0 END)as Total_comm
from
#inds i inner join casevictims v on i.personid=v.victimid
inner join mandate_correspondence c on v.caseid=c.caseid
and i.[month]=month(c.datecomposed) and i.[year]=year(c.DateComposed)
inner join mandate_casemandates m on c.caseid=m.caseid
inner join mandates ma on m.mandateid=ma.mandateid
group by year,mandate
order by year,mandate

thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 13:07:38

At a first glimpse, put the UNION query in a derived table and GROUP BY later.
The two selects does not have the same amount of columns either.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 13:13:51

[code]select [Year],
Mandate,
'UA' = SUM(CASE urgent WHEN 'Y' THEN 1 ELSE 0 END),
0 as JUA,
'AL' = SUM(CASE urgent WHEN 'N' THEN 1 ELSE 0 END),
0 as JAL
count(*) as Total_comm
from #inds as i
inner join missingperson as mp on mp.missingpersonid = i.personid
inner join missingperson_correspondence as c on c.missingpersonid = mp.missingpersonid

where Mandate = 'WGEID'
and urgent in ('Y','N')
and i.[month] = month(c.transmitdate)
and i.[year] = year(c.transmitdate)
group by year,
Mandate

UNION all

select [Year],
Mandate,
'UA' = SUM(CASE CorrespondenceType WHEN 'UA' THEN 1 ELSE 0 END),
'JUA' = SUM(CASE CorrespondenceType WHEN 'JUA' THEN 1 ELSE 0 END),
'AL' = SUM(CASE CorrespondenceType WHEN 'AL' THEN 1 ELSE 0 END),
'JAL' = SUM(CASE CorrespondenceType WHEN 'JAL' THEN 1 ELSE 0 END),
SUM(CASE when CorrespondenceType in ('UA','JAL','JUA','AL') THEN 1 ELSE 0 END) as Total_comm
from #inds as i
inner join casevictims as v on i.personid=v.victimid
inner join mandate_correspondence as c on v.caseid=c.caseid
inner join mandate_casemandates m on c.caseid=m.caseid
inner join mandates ma on m.mandateid=ma.mandateid
where CorrespondenceType in ('UA','JAL','JUA','AL')
and i.[month] = month(c.datecomposed)
and i.[year] = year(c.DateComposed)
group by year,
mandate

order by 1,
2[/code]

E 12°55'05.25"
N 56°04'39.16"

Mari
Starting Member

19 Posts

Posted - 2007-08-30 : 13:14:30

Thanks for replying , Peso, but what do you mean in a derived table? I took out the group by in the first statement and it gives this error:

Server: Msg 8118, Level 16, State 1, Line 1
Column 'i.Year' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 13:18:12

As a good practice, always prefix your column names with table name.

E 12°55'05.25"
N 56°04'39.16"

Mari
Starting Member

19 Posts

Posted - 2007-08-30 : 13:22:07

did add the 'Where Mandate = 'WGEID' where you put it, but still gives the same error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Mandate'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'mandate'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 13:41:22

Do you care to post the FULL query you use as of now?

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-30 : 13:45:21

Prefix Mandate with the table it comes from. And make sure that it is actually in that table, spelled correctly.

As Peso said, always -- without exception -- prefix column names in a multi-table SELECT with what table they come from. Always.

- Jeff
http://weblogs.sqlteam.com/JeffS

Mari
Starting Member

19 Posts

Posted - 2007-08-31 : 06:01:22

Here agian is my full query with no prefix but full name tables.
Thanks!

select
[Year],Mandate,
'UA'=SUM(CASE urgent WHEN 'Y' THEN 1 ELSE 0 END),
'' as JUA,
'AL'=SUM(CASE urgent WHEN 'N' THEN 1 ELSE 0 END),
'' as JAL,
SUM(CASE when urgent in ('Y','N') THEN 1 ELSE 0 END)as Total_comm
from
#inds inner join missingperson on #inds.personid=missingperson.missingpersonid
inner join missingperson_correspondence on missingperson.missingpersonid=missingperson_correspondence.missingpersonid
where Mandate = 'WGEID'
and #inds.[month]=month(missingperson_correspondence.transmitdate) and #inds.[year]=year(missingperson_correspondence.transmitdate)
group by year,mandate
UNION all
select
[Year], Mandate,
'UA'=SUM(CASE CorrespondenceType WHEN 'UA' THEN 1 ELSE 0 END),
'JUA'=SUM(CASE CorrespondenceType WHEN 'JUA' THEN 1 ELSE 0 END),
'AL'=SUM(CASE CorrespondenceType WHEN 'AL' THEN 1 ELSE 0 END),
'JAL'=SUM(CASE CorrespondenceType WHEN 'JAL' THEN 1 ELSE 0 END),
SUM(CASE when CorrespondenceType in ('UA','JAL','JUA','AL') THEN 1 ELSE 0 END)as Total_comm
from
#inds inner join casevictims on #inds.personid=casevictims.victimid
inner join mandate_correspondence on casevictims.caseid=mandate_correspondence.caseid
and #inds.[month]=month(mandate_correspondence.datecomposed) and #inds.[year]=year(mandate_correspondence.DateComposed)
inner join mandate_casemandates m on mandate_correspondence.caseid=m.caseid
inner join mandates ma on m.mandateid=ma.mandateid
group by year,mandate
order by year,mandate

Mari
Starting Member

19 Posts

Posted - 2007-08-31 : 06:05:02

Sorry, here again is the full query, forget to change other prefix to full table name:

select
[Year],Mandate,
'UA'=SUM(CASE urgent WHEN 'Y' THEN 1 ELSE 0 END),
'' as JUA,
'AL'=SUM(CASE urgent WHEN 'N' THEN 1 ELSE 0 END),
'' as JAL,
SUM(CASE when urgent in ('Y','N') THEN 1 ELSE 0 END)as Total_comm
from
#inds inner join missingperson on #inds.personid=missingperson.missingpersonid
inner join missingperson_correspondence on missingperson.missingpersonid=missingperson_correspondence.missingpersonid
where Mandate = 'WGEID'
and #inds.[month]=month(missingperson_correspondence.transmitdate) and #inds.[year]=year(missingperson_correspondence.transmitdate)
group by year,mandate
UNION all
select
[Year], Mandate,
'UA'=SUM(CASE CorrespondenceType WHEN 'UA' THEN 1 ELSE 0 END),
'JUA'=SUM(CASE CorrespondenceType WHEN 'JUA' THEN 1 ELSE 0 END),
'AL'=SUM(CASE CorrespondenceType WHEN 'AL' THEN 1 ELSE 0 END),
'JAL'=SUM(CASE CorrespondenceType WHEN 'JAL' THEN 1 ELSE 0 END),
SUM(CASE when CorrespondenceType in ('UA','JAL','JUA','AL') THEN 1 ELSE 0 END)as Total_comm
from
#inds inner join casevictims on #inds.personid=casevictims.victimid
inner join mandate_correspondence on casevictims.caseid=mandate_correspondence.caseid
and #inds.[month]=month(mandate_correspondence.datecomposed) and #inds.[year]=year(mandate_correspondence.DateComposed)
inner join mandate_casemandates on mandate_correspondence.caseid=mandate_casemandates.caseid
inner join mandates on mandate_casemandates.mandateid=mandates.mandateid
group by year,mandate
order by year,mandate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 07:11:54

Thank you for that. What is now the problem with the output?

E 12°55'05.25"
N 56°04'39.16"

Mari
Starting Member

19 Posts

Posted - 2007-08-31 : 08:49:39

it still gives the same error (for the first statement:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Mandate'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'mandate'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 08:59:46

[code]select <missing table name (or table alias) here>.[Year],
<missing table name (or table alias) here>.Mandate,
'UA' = SUM(CASE <missing table name (or table alias) here>.urgent WHEN 'Y' THEN 1 ELSE 0 END),
0 as JUA,
'AL' = SUM(CASE <missing table name (or table alias) here>.urgent WHEN 'N' THEN 1 ELSE 0 END),
0 as JAL,
count(*) as Total_comm
from #inds
inner join missingperson on #inds.personid = missingperson.missingpersonid
inner join missingperson_correspondence on missingperson.missingpersonid = missingperson_correspondence.missingpersonid
where <missing table name (or table alias) here>.Mandate = 'WGEID'
and #inds.[month] = month(missingperson_correspondence.transmitdate)
and #inds.[year] = year(missingperson_correspondence.transmitdate)
and <missing table name (or table alias) here>.urgent in ('Y','N')
group by <missing table name (or table alias) here>.year,
<missing table name (or table alias) here>.mandate

UNION all

select <missing table name (or table alias) here>.[Year],
<missing table name (or table alias) here>.Mandate,
'UA' = SUM(CASE <missing table name (or table alias) here>.CorrespondenceType WHEN 'UA' THEN 1 ELSE 0 END),
'JUA' = SUM(CASE <missing table name (or table alias) here>.CorrespondenceType WHEN 'JUA' THEN 1 ELSE 0 END),
'AL' = SUM(CASE <missing table name (or table alias) here>.CorrespondenceType WHEN 'AL' THEN 1 ELSE 0 END),
'JAL' = SUM(CASE <missing table name (or table alias) here>.CorrespondenceType WHEN 'JAL' THEN 1 ELSE 0 END),
COUNT(*) as Total_comm
from #inds
inner join casevictims on #inds.personid = casevictims.victimid
inner join mandate_correspondence on casevictims.caseid = mandate_correspondence.caseid
and #inds.[month] = month(mandate_correspondence.datecomposed)
and #inds.[year] = year(mandate_correspondence.DateComposed)
inner join mandate_casemandates on mandate_correspondence.caseid = mandate_casemandates.caseid
inner join mandates on mandate_casemandates.mandateid = mandates.mandateid
where <missing table name (or table alias) here>.CorrespondenceType in ('UA','JAL','JUA','AL')
group by <missing table name (or table alias) here>.year,
<missing table name (or table alias) here>.mandate

order by 1,
2[/code]

E 12°55'05.25"
N 56°04'39.16"

Mari
Starting Member

19 Posts

Posted - 2007-09-03 : 05:05:50

Just to let you know, I had to work around it by inserting the second table into another temp table and do the union between the new temp table and the first statement. It works ok this way but still can't group by and order by 'mandate' :

select
[Year], Mandate,
'UA'=SUM(CASE CorrespondenceType WHEN 'UA' THEN 1 ELSE 0 END),
'JUA'=SUM(CASE CorrespondenceType WHEN 'JUA' THEN 1 ELSE 0 END),
'AL'=SUM(CASE CorrespondenceType WHEN 'AL' THEN 1 ELSE 0 END),
'JAL'=SUM(CASE CorrespondenceType WHEN 'JAL' THEN 1 ELSE 0 END),
SUM(CASE when CorrespondenceType in ('UA','JAL','JUA','AL') THEN 1 ELSE 0 END)as Total_comm
from
#inds inner join casevictims on #inds.personid=casevictims.victimid
inner join mandate_correspondence c on casevictims.caseid=mandate_correspondence.caseid
and #inds .[month]=month(mandate_correspondence.datecomposed) and #inds.[year]=year (mandate_correspondence.DateComposed)
inner join mandate_casemandates on mandate_correspondence.caseid=mandate_casemandates.caseid
inner join mandates on mandate_casemandates.mandateid=mandates.mandateid
group by [year],mandate
order by [year],mandate

select
[Year], Mandate='WGEID',
'UA'=SUM(CASE urgent WHEN 'Y' THEN 1 ELSE 0 END),
''as JUA,
'AL'=SUM(CASE urgent WHEN 'N' THEN 1 ELSE 0 END),
'' as JAL,
SUM(CASE when urgent in ('Y','N') THEN 1 ELSE 0 END)as Total_comm
from
#inds inner join missingperson on #inds.personid=missingperson.missingpersonid
inner join missingperson_correspondence on missingperson.missingpersonid=missingperson_correspondence.missingpersonid
and #inds.[month]=month(missingperson_correspondence.transmitdate) and #inds.[year]=year(missingperson_correspondence.transmitdate)
group by [year]
UNION ALL
select
year,mandate,ua,al,jal,jua,total_comm
from
#inds2
order by [year]

anyway thanks for the help, Peso!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 05:22:32

Thank you!
Since you still refuse to prefix the column, I feel no obligation to help you further.

E 12°55'05.25"
N 56°04'39.16"

Mari
Starting Member

19 Posts

Posted - 2007-09-04 : 05:17:33

Hi, can't prefix column because it's a virtual column for the first statement.
'Mandate' column doesn't belong to any table in the first statement.

Thanks, anyway! :-)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 05:21:47

Even if the column is "virtual", you can group by the underlying statement!
And use a numeric ORDER BY.

E 12°55'05.25"
N 56°04'39.16"

Mari
Starting Member

19 Posts

Posted - 2007-09-04 : 06:19:46

YES!!! wonderful, now it's working! Thanks a lot for your patience, Peso, now I understand what you mean! took me some time, sorry about that :-) Really learning a lot from you! Have a great day!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources