Group By Question

[oesku]

Hi guys,

I need assistance for this kind of sql result. I'm quite inexperienced with aggregate functions and grouping data after a selection. So here is what I get from my query so far:

Year Total Format
-------------------------------
2002 1341 PDF
2002 77 PALM
2002 1 AUDIO
2003 6596 PDF
2003 381 PALM
2003 3 AUDIO
2003 53 MSR

Indeed, what I'd like to have is such a result:
What I would like to achieve is this kind of bag:
Year PDF PALM AUDIO MSR ... ...
2002 1341 77 1 n/a
2003 6596 381 3 53 ... ...

And here's the sql:

SELECT Year(sendDate) as MonthYear, COUNT(abstractTypeId) AS CountAbstractTypes, masterDataAbstractTypes.name as Format FROM masterDataAbstractTypes INNER JOIN downloadHistory ON downloadHistory.abstractTypeId = masterDataAbstractTypes.id GROUP BY Year(sendDate),abstractTypeId order by MonthYear

 

[r937]

that's called a "pivot" or "crosstab" layout, and you should be doing that in your application language, not in the database extract

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jaxtell]

I can't seem to come up with a single query solution for your problem, but I can do it in two. The first query would build the second. Here is some code. Please excuse me if the sql isn't valid in mysql, most of my work is in oracle.

select distinct name from masterDataAbstractTypes;

lets say that gets loaded into a resultset in java

StringBuffer myQuery = new StringBuffer();
rs.next();
myQuery.append("SELECT Year(sendDate) as MonthYear, COUNT(abstractTypeId) AS CountAbstractTypes, masterDataAbstractTypes.name as Format  FROM masterDataAbstractTypes INNER JOIN downloadHistory  ON downloadHistory.abstractTypeId = masterDataAbstractTypes.id
WHERE masterDataAbstractTypes.name = '" + rs.getString("NAME") +"' GROUP BY Year(sendDate),abstractTypeId order by MonthYear");
while (rs.next()){
    myQuery.append(" UNION ALL SELECT Year(sendDate) as MonthYear, COUNT(abstractTypeId) AS CountAbstractTypes, masterDataAbstractTypes.name as Format  FROM masterDataAbstractTypes INNER JOIN downloadHistory  ON downloadHistory.abstractTypeId = masterDataAbstractTypes.id
WHERE masterDataAbstractTypes.name = '" + rs.getString("NAME") +"' GROUP BY Year(sendDate),abstractTypeId order by MonthYear ");
}
//convert myQuery and execute

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[jaxtell]

Eh nevermind that last post. I haven't had my coffee yet.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[jaxtell]

let me try that again

StringBuffer myQuery = new StringBuffer();
rs.next();
myQuery.append(" select MonthYear, case Format when '" + rs.getString("NAME") +"' then CountAbstractTypes else 0 as '" + rs.getString("NAME") +"' ");
while (rs.next()){
myQuery.append(", case Format when '" + rs.getString("NAME") +"' then CountAbstractTypes else 0 as '" + rs.getString("NAME") +"' ");
}
myQuery.append(" from (
SELECT Year(sendDate) as MonthYear, COUNT(abstractTypeId) AS CountAbstractTypes, masterDataAbstractTypes.name as Format  FROM masterDataAbstractTypes INNER JOIN downloadHistory  ON downloadHistory.abstractTypeId = masterDataAbstractTypes.id
 GROUP BY Year(sendDate),abstractTypeId order by MonthYear) group by MonthYear");

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[oesku]

Hi guys,

Thanks for having received all your suggestions, I'll report on the results later. Funny enough is the fact that when planning the project I recommended to start with a new data base in order to set up kind of business intelligence system, appropriate to the needs and scale of the business. But due to an incredible lack of understanding the domain I have to struggle now with data manipulation with java and generating xml data in order to transfer them to a nice looking flash chart component. Hmm, sometimes IT departments and their decision makers are weird..

cheers

 

[r937]

whaddya mean, "sometimes"

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[oesku]

Here's a simplified version of the stringbuffer:

select MonthYear, case when Format='PDF' then CountAbstractTypes else 0 end as PDF, case when Format='PALM' then CountAbstractTypes else 0 end as PALM from (SELECT Year(sendDate) as MonthYear, COUNT(abstractTypeId) AS CountAbstractTypes, masterDataAbstractTypes.name as Format FROM masterDataAbstractTypes INNER JOIN downloadHistory ON downloadHistory.abstractTypeId = masterDataAbstractTypes.id WHERE dataId <> 0 AND TO_DAYS(sendDate) >= TO_DAYS('2006-2-01') and TO_DAYS(sendDate) <= TO_DAYS('2009-2-28') and corporateId = 119 and downloadHistory.customerId >0 GROUP BY Year(sendDate),abstractTypeId order by MonthYear) as DerivedTable1 group by MonthYear

Unfortunately, the PDF's count is ok, but then Palms are not counted. I assume I'm making an error with case switcher, closing and opening a new one...
The changes I made are: Added a where clause and other conditions, inserted a table alias (DerivedTable1) to the subquery at the end of the statement, wrote an "end" keyword before the abstractType aliases and added an equals sign instead of "case format when "[abstractType]"

Do you have an idea where the error occurs?
Thanks

 

[r937]

SELECT Year(sendDate) as MonthYear
     , COUNT(CASE WHEN m.name = 'PDF'
                  THEN 0 END) AS PDF
     , COUNT(CASE WHEN m.name = 'PALM'
                  THEN 0 END) AS PALM
     , COUNT(CASE WHEN m.name = 'AUDIO'
                  THEN 0 END) AS AUDIO
     , ...
  FROM masterDataAbstractTypes AS m
INNER
  JOIN downloadHistory AS d
    ON d.abstractTypeId = m.id 
GROUP 
    BY Year(sendDate)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[oesku]

Far too kind! Great, the last one did the job, perfect!

It just looks so simple and straightforward, I feel so dumb, but I know there is a lot of know-how and expert knowledge in that..cool.

cheers and many thanks to all.