Posts Tagged with from

Displaying 1-6 of 6 results.
Resolved: MySQL - Count Number of Unique Values
posted by admin on May 21, 2016
If I have three columns:

orderNumber, name, email

and I would like to count how many unique emails are in the table how would I go about doing so?

A statement like:

SELECT count(email) FROM orders

gives me the total count.
I tried

SELECT DISTINCT count(email) FROM orders

but that does not seem to be giving me the numbers I am expecting.Read more
Resolved: How to get the latest record in each group using GROUP BY?
posted by admin on May 21, 2016
Let's say I have a table called messages with the columns:

id | from_id | to_id | subject | message | timestamp


I want to get the latest message from each user only, like you would see in your FaceBook inbox before you drill down into the actual thread.

This query seems to get me close to the result I need:

SELECT * FROM messages GROUP BY from_id


However the query is giving me the oldest message from each user and not the newest.

I can't figure this one out.Read more
Resolved: Get data from table from a list of strings on MySQL
posted by admin on May 6, 2016
How can I query data from a table available on a list of strings?

I only want to get the data from the list of strings.

Example:
Table
ID Name
1 Big
2 Small
3 Extra
4 Orange
5 Drink



List of Strings:
Big
Small
Extra
Read more
Resolved: MySQL IN for all values
posted by admin on May 6, 2016
I have MySQL query, which contain IN clause

select ItemID
from ItemCategory
where CategoryID in (5,6,7,8)
group by ItemID


I need it to work like an AND across multiple rows.Read more
Resolved: Yii sum criteria select condition
posted by admin on April 15, 2016
I try to count my views sum count, by this way

$criteria=new CDbCriteria;
$criteria->select='SUM(visits) as SUMvisits';
$criteria->condition='media_id=:media_id';
$criteria->params=array(':media_id'=>$media_id);
$sBalance = Views::model()->find($criteria)->getAttribute('SUMvisits');
var_dump($sBalance);exit();


Something like this, but it is not workingRead more
Resolved: MySQL задание менеджер, продукт, заявки, продажы. Задача из собеседования - PHP разработчик
posted by admin on March 16, 2016
Даны следующие две таблицы:

--
-- Структура таблицы `claim`
--
CREATE TABLE IF NOT EXISTS `claim` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`manager_id` int(11) NOT NULL,
`created_at` datetime NOT NULL,
`sum` float NOT NULL,
PRIMARY KEY (`id`),
KEY `manager_id` (`manager_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
--
-- Дамп данных таблицы `claim`
--

INSERT INTO `claim` (`id`, `manager_id`, `created_at`, `sum`) VALUES
(1, 1, '2013-07-18 10:23:08', 256.128),
(2, 3, '2013-07-18 13:29:49', 512.1),
(3, 4, '2013-07-19 16:29:07', 123),
(4, 4, '2013-07-18 17:35:53', 321),
(5, 4, '2013-06-19 15:31:46', 756),
(6, 1, '2013-06-05 10:47:26', 265),
(7, 2, '2013-05-31 20:27:38', 354),
(8, 4, '2013-07-17 15:48:20', 798.12);
----------------------------------------------------------

--
-- Структура таблицы `manager`
--
CREATE TABLE IF NOT EXISTS `manager` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(32) CHARACTER SET latin1 NOT NULL,
`last_name` varchar(32) CHARACTER SET latin1 NOT NULL,
`email` varchar(32) CHARACTER SET latin1 NOT NULL,
`chief_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `chief_id` (`chief_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
--
-- Дамп данных таблицы `manager`
--
INSERT INTO `manager` (`id`, `first_name`, `last_name`, `email`, `chief_id`) VALUES
(1, 'Simple', 'Manager', '', 2),
(2, 'Super', 'Manager', '', NULL),
(3, 'Third ', 'Manager', '', 2),
(4, 'Just', 'Manager', '', NULL);

--
-- Ограничения внешнего ключа таблицы `claim`
--
ALTER TABLE `claim`
ADD CONSTRAINT `claim_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `manager` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Ограничения внешнего ключа таблицы `manager`
--
ALTER TABLE `manager`
ADD CONSTRAINT `manager_ibfk_1` FOREIGN KEY (`chief_id`) REFERENCES `manager` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;


Задача 1

Напишите sql-запрос, выбирающий информацию по каждому менеджеру, включая количество связанных с ним заявок и их общую сумму (в 2 дополнительных поля: claim_count, claim_total_sum).


Задача 2

Напишите запрос, который выведет двух менеджеров, у которых количество связанных заявок меньше, чем у остальных. При этом, объедините значения first_name и last_name в одно поле full_name.
Read more