combine 3 queries into 1

[poe]

hello,

 

I had previously posted about adding qty from 3 separate tales in 1 query.

 

my new question is :

 

can i run 3 separate queries within the same single query on just 1 table ?

 

i have a table with :

 

LID, YEAR, MONTH, WEEK, HITS

1      2010    01      02      50

1      2009    12      52      10

1      2009    12      51      20

1      2009    12      53      15

2      2009    12      52      75

2      2009    12      51      30

3      2009    12      52      10

3      2009    12      50      75

 

the result i want is

lid 1, ttl hits for month 12 = 35, ttl hits for 2009 = 35, ttl hits wk > 51 = 25

lid 2 .....

[kickstart]

Hi

 

You could do it like this:-

 

SELECT a.LID, a.YEAR, a.YearTotal, b.YearMonth, b.YearMonthTotal, c.YearWeek, c.YearWeekTotal
FROM (SELECT LID, YEAR, SUM(HITS) AS YearTotal FROM someTable GROUP BY LID, YEAR) a
JOIN (SELECT LID, CONCAT(YEAR,' ',MONTH) AS YearMonth, SUM(HITS) AS YearTotal FROM someTable GROUP BY LID, YEAR, MONTH) b
ON a.LID = b.LID
JOIN (SELECT LID, CONCAT(YEAR,' ',WEEK) AS YearWeek, SUM(HITS) AS YearTotal FROM someTable GROUP BY LID, YEAR, WEEK) c
ON a.LID = c.LID

 

Although not sure whether it would efficiency wise be worthwhile most of the time.

 

All the best

 

Keith