5 replies to this topic

[cancer10]

Hello

I am looking for some mysql query assistance from you experts :)


I have a table that stores all the download logs of software on my website.

The table structure is as below:

CREATE TABLE `software_downloads` (

  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  `software_id` INT(10) DEFAULT NULL,

  `download_date` DATETIME DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MYISAM DEFAULT CHARSET=latin1

So what I am looking for is, when I generate reports, my customers should be able to group the dates by quarterly and half yearly. That means, if they choose the quarterly option, the report should list all the count of software download logs and group them by three months and six months in case if they choose the half-yearly option.


Thanks in advance for your help.

[WingedPanther]

There are a couple ways you can do this:
1) issue requests for the aggregate data on a date range by date range basis
2) request all the data sorted by date and let your reporting tool handle adding summary data breaks at the appropriate point.
3) if you don't really care about the data, you could add an additional column for the quarter/half-year and use that for a group by clause.

The best choice will depend on the details of what's going on with your report.

[cancer10]

I actually want the date to group by Jan-Mar, Apr-Jun, July-Sep, Oct-Dec.

Please help me with the query

[cancer10]

One more query.

Please consider the following DDL

CREATE TABLE `software_downloads` (

  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  `software_id` INT(10) DEFAULT NULL,

  `download_by` VARCHAR(10) NOT NULL,

  `download_date` DATETIME DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MYISAM DEFAULT CHARSET=latin1

Here I added a new field "download_by", The values for this column will either be "admin" or "customer". so will it be possible to find out how many of the downloads are made by admin and how many of them are made by customers withing the same query?


Thanks

[WingedPanther]

select count(*), download_by from software_downloads group by download_by

[cancer10]

Thank you very much for your kind help.