Geek Answers Handbook

Number of numeric lines for multiple values

Consider this table, which indicates how many times a person has bought a property.

+--------+----------+ | user | property | +--------+----------+ | john | car | | john | car | | john | house | | peter | car | | peter | car | | amanda | house | | amanda | house | +--------+----------+

I need to know how many times a car was bought once, how many times I bought a house once, etc. Something like that:

 +----------+---+---+ | property | 1 | 2 | +----------+---+---+ | cars | 4 | 2 | | house | 3 | 1 | +----------+---+---+
  • How many times was a car bought? Four , two for peter and two for john .
  • How many times was a car bought twice? Two , for the same guys.
  • How many times was a house purchased? Three , two for amanda and once for john .
  • How many times has a house been bought twice? Only once , for amanda

Can this be done only with SQL queries?

  • I don't care about performance or hacking methods.
  • There are more than two frequencies.

  • There is a fixed set of times when a person can buy property (5), so you do not need to specify columns manually in the query. I mean, there is no problem with something like:

     SELECT /* ... */ AS 1, /* ... */ AS 2, /* ... */, AS 3 /* ... */
+4
source share
7 answers
 SELECT DISTINCT @pr := prop, (SELECT COUNT(1) FROM tbl WHERE prop = @pr LIMIT 1), (SELECT COUNT(1) FROM (SELECT *, COUNT(*) cnt FROM tbl GROUP BY usr, prop HAVING cnt = 2) as tmp WHERE `tmp`.prop = @pr LIMIT 1) FROM tbl;

Yes, this is not the best method; but hey, you get the answers you want.

In addition, it will generate results for any property in your table.

The link to the violin is here .

PS: 60 attempts O_O

+1
source

I am here since you posted a question. Good...
Here's a way to do it exactly as you requested, with just groups and accounts.
The trick is that I combine user columns and properties to create a unique "id" for each, if we could call it. It should work regardless of the number of purchases.

 SELECT C.`property`, COUNT(C.`property`), D.`pcount` from `purchases` C LEFT JOIN( SELECT A.`property`, B.`pcount` FROM `purchases` A LEFT JOIN ( SELECT `property`, CONCAT(`user`, `property`) as conc, COUNT(CONCAT(`user`, `property`)) as pcount FROM `purchases` GROUP BY CONCAT(`user`, `property`) ) B ON A.`property` = B.`property` GROUP BY B.pcount ) D ON C.`property` = D.`property` GROUP BY C.`property`
+1
source

SQL Fiddle

MySQL 5.5.30 Schema setup :

 CREATE TABLE Table1 (`user` varchar(6), `property` varchar(5)) ; INSERT INTO Table1 (`user`, `property`) VALUES ('john', 'car'), ('john', 'car'), ('john', 'house'), ('peter', 'car'), ('peter', 'car'), ('amanda', 'house'), ('amanda', 'house') ;

Request 1 :

 select t.property, t.total, c1.cnt as c1, c2.cnt as c2, c3.cnt as c3 from (select t.property , count(t.property) as total from Table1 t group by t.property ) as t left join ( select property, count(*) as cnt from ( select property, user, count(*) as cnt from table1 group by property, user having count(*) = 1 ) as i1 group by property ) as c1 on t.property = c1.property left join ( select property, count(*) as cnt from ( select property, user, count(*) as cnt from table1 group by property, user having count(*) = 2 ) as i2 group by property ) as c2 on t.property = c2.property left join ( select property, count(*) as cnt from ( select property, user, count(*) as cnt from table1 group by property, user having count(*) = 3 ) as i3 group by property ) as c3 on t.property = c3.property

Results :

 | PROPERTY | TOTAL | C1 | C2 | C3 | ------------------------------------------- | car | 4 | (null) | 2 | (null) | | house | 3 | 1 | 1 | (null) |
+1
source

You can try the following.

 SELECT COUNT(TABLE1.PROPERTY) AS COUNT, PROPERTY.USER FROM TABLE1 INNER JOIN (SELECT DISTINCT PROPERTY, USER FROM TABLE1) AS PROPERTY ON PROPERTY.PROPERTY = TABLE1.PROPERTY AND PROPERTY.USER = TABLE1.USER GROUP BY TABLE1.USER, PROPERTY.PROPERTRY

checked similarly in MySQL

+1
source

try it

  SELECT property , count(property) as bought_total , count(distinct(user)) bought_per_user FROM Table1 GROUP BY property

the output will be like

  PROPERTY | BOUGHT_TOTAL | BOUGHT_PER_USER ________________________________________________________ car | 4 | 2 house | 3 | 2

DEMO SQL FIDDLE HERE

0
source

You can do this with sub-selections.

 SELECT property, user, COUNT(*) FROM purchases GROUP BY property, user;

will return you a complete set of grouped data that you want. Then you need to look at different frequencies:

 SELECT property, freq, COUNT(*) FROM (SELECT property, user, COUNT(*) freq FROM purchases GROUP BY property, user) AS foo GROUP BY property, freq;

This is not quite in the format you illustrated, but it returns data

0
source

Hope this helps you ... let's create one table first:

create table prop (custom varchar (max), varchar (max) property)

enter the values ​​of prop ('john', 'car'), insert the values ​​of prop ('john', 'car'), insert into the values ​​of prop ('john', 'house'), insert into the values ​​of prop ('peter', ' car '),

enter the values ​​of prop ('peter', 'car'), insert the values ​​of prop ('amanda', 'house'), insert the values ​​of prop ('amanda', 'house')

1) how many times was the car purchased?

ANS: select count (property) from prop, where property = 'car' (4)

2) How many times has the car been bought twice?

ANS: select user, COUNT (property) from prop, where property = 'car' group by user having COUNT (property) = 2

2-John 2-Peter

3) How many times have I bought a house?

ANS: select COUNT (property) from prop, where property = 'house' (3) 4) How many times has the house been bought twice? ANS: select the user, COUNT (property) from prop, where property = 'house' group the user having COUNT (property) = 2 2-Amanda 1-John

0
source

More articles:


All Articles