General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Need assistance with Select statement
I have the following statement
SELECT DISTINCT (case when season = 'fall' then COUNT(bfblue_data.FAIR_ID) else 0 end) as falltotal ,
(case when season = 'spring' then COUNT(bfblue_data.FAIR_ID) else 0 end) as springtotal ,
(case when jobdesc = 'SBF4154 (Blue, V2)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v2total ,
(case when jobdesc = 'SBF4154 (Red, V3)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v3total ,
(case when jobdesc = 'SBF4154 (Red, V1)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v1total , master.Region, bfblue_data.jobDesc, master.season, 'all' AS type
FROM bfblue_data INNER JOIN
master ON bfblue_data.FAIR_ID = master.FairNumber
GROUP BY master.Region, bfblue_data.jobDesc, master.season
UNION
SELECT DISTINCT
(case when season = 'fall' then COUNT(bfblue_data.FAIR_ID) else 0 end) as falltotal ,
(case when season = 'spring' then COUNT(bfblue_data.FAIR_ID) else 0 end) as springtotal ,
(case when jobdesc = 'SBF4154 (Blue, V2)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v2total ,
(case when jobdesc = 'SBF4154 (Red, V3)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v3total ,
(case when jobdesc = 'SBF4154 (Red, V1)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v1total ,
master.Region, bfblue_data.jobDesc, master.season, 'week' AS type
FROM bfblue_data INNER JOIN
master ON bfblue_data.FAIR_ID = master.FairNumber AND bfblue_data.updateddate > dateadd(day,-30,getdate())
GROUP BY master.Region, bfblue_data.jobDesc, master.season
ORDER BY type, master.season,master.Region, bfblue_data.jobDesc
Results
falltotal springtotal v2total v3total v1total Region jobDesc season type
----------- ----------- ----------- ----------- ----------- ----------- -------------------- ---------- ----
582 0 582 0 0 1 SBF4154 (Blue, V2) fall all
181 0 0 0 181 1 SBF4154 (Red, V1) fall all
34 0 0 34 0 1 SBF4154 (Red, V3) fall all
And what I would like to do is group the result set from the above statement. Any ideas?? If I was using Foxpro i would just run the first select statement into a cursor and then run another againt the cursor to group it again. I dont know how to do that in SQL Server..
Thanks
Paul
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only