j***3
发帖数: 142 | 1 I have a table like this:
color size fruit
red big apple
red small apple
green big apple
yellow small orange
red small orange
when I do:
select color, size, count(*) group by color, size;
I got:
red big 1
red small 2
green big 1
yellow small 1
but what I need is to also return the NULL group count such as:
red big 1
red small 2
green big 1
green small 0
yellow small 0
yellow small 1
how I can do the query in mysql?
thank you all. | d*******o
发帖数: 493 | 2 select c.color, c.size, count(yourtable.fruit)
from yourtable
full join
(select distinct a.color, b.size
from yourtable as a, yourtable as b) as c
on yourtable.color=c.color and yourtable.size=c.size
group by c.color, c.size | s******y
发帖数: 352 | 3 what you can do is:
first create temp table which is a resultant set of cartesian join
color and size. then left join the temp table with your summary table.
create view temp as
select * from (select color from xxxx,select size from xxx);
select temp.* , cnt from temp
left join
(select color, size, count(*) as cnt group by color, size)
;
you have to verify the Mysql syntax. but the idea should be implemented
easily. | j***3
发帖数: 142 | 4 thank you dapangmao,
but I could not full understand what you wrote:
since yourtable as a
then what is b?
【在 d*******o 的大作中提到】
: select c.color, c.size, count(yourtable.fruit)
: from yourtable
: full join
: (select distinct a.color, b.size
: from yourtable as a, yourtable as b) as c
: on yourtable.color=c.color and yourtable.size=c.size
: group by c.color, c.size
| j***3
发帖数: 142 | 5 thanks smileguy, I will try
【在 s******y 的大作中提到】
: what you can do is:
: first create temp table which is a resultant set of cartesian join
: color and size. then left join the temp table with your summary table.
: create view temp as
: select * from (select color from xxxx,select size from xxx);
: select temp.* , cnt from temp
: left join
: (select color, size, count(*) as cnt group by color, size)
: ;
: you have to verify the Mysql syntax. but the idea should be implemented
| R*********r
发帖数: 225 | 6 SQL SERVER VERSION:
CREATE TABLE dbo.Produce (
color varchar(20),
size varchar(20),
fruit varchar(20)
)
INSERT INTO dbo.Produce (
color,
size,
fruit
)
VALUES
( 'red', 'big', 'apple'),
('red' , 'small', 'apple' ),
('green', 'big' , 'apple' ) ,
('yellow', 'small', 'orange' ) ,
('red' , 'small', 'orange')
WITH Temp AS (
SELECT * FROM
(SELECT DISTINCT color FROM Produce) AS X CROSS JOIN
(SELECT DISTINCT size FROM Produce) AS Y
) | j***3
发帖数: 142 | 7 thank you RProgrammer (and smileguy, dapangmao again).
you guys are awesome !
I did not expect a simple query in mysql could go so complicated. |
|
